Can we safely use cross-sheet references in column formula?

Hello everyone,

I have a fairly large sheet (~5000 rows). I have one column whose value gets changed based on data from another column within the sheet. The source column is a country code column and the column that gets updated displays region based on the country code (EMEA, LATAM, NA, etc.). I decided to create another helper sheet that holds the mappings from country codes to regions (and it also holds some other helper columns that allow me to customize my sheet).

To fill in the region column based on country code column, I've created a VLOOKUP formula like this: =VLOOKUP([Country Code]@row, {Regions [ALL 3]}, 3), where {Regions [ALL 3]} is a reference to the helper sheet that has 3 columns (Country Code, Region, Center). So this is where I've run into a problem, I'm getting a #NOMATCH error on some rows even though the values are 100% correct and the mapping should work. After investigating, it appears that there is a cross-reference sheet limit. My question is, could it be the limit causing this? How would I check if it is and if so, is there a better workaround other than a complex nested IF() statement? It would be amazing if I could avoid using IF's, because otherwise I'd have almost 80 nested IF statements.

Cheers

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Andrée Starå

Hi @mirko

I hope you're well and safe!

You'd get warnings if you're close to the limits, so I don't think that's it, but the complexity of the solution/structure can also have an impact.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mirko

Hello @Andrée Starå

As of right now, we have about 22 columns and ~4900 rows. So the complexity might in fact be the reason for SmartSheet not working properly.

I have provided a screenshot of columns that are relevant to my question bellow.

First column is the source column. It is a column with country codes. The pink columns are hidden and are mapping data from the first column to the helper sheet's columns' data.

Third column, or Sub-Region column maps the first (country code) column to its region via the external helper sheet. The screenshot of said helper sheet will be provided below. The formula for this column is VLOOKUP([Country Code]@row, {Regions [ALL 3]}, 2).

Fourth column, or Main Jurisdiction column also uses the helper sheet. It uses lookup to cross-reference the country code to the Main Jurisdiction column from the helper sheet. This column is used as a helper column when creating reports. The formula for this column is VLOOKUP([Country Code]@row, {Regions [ALL 3]}, 3).

Finally, the second column or Region column checks if the Sub-Region column is also a helper column, its used in reports that need more granular distinctions between regions. The formula for this column is IF(OR([Sub-Region]@row = "US", [Sub-Region]@row = "CA"), "NA", [Sub-Region]@row).

Below I will provide a screenshot of the errors I'm getting. I've checked the first column and it has no typos or any other inconsistencies in its formatting.

Hopefully I was clear enough in my explanation.

Many thanks,

Mirko

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.