Unique cells referenced in cross-sheet formulas Limit - How is this calculated?
I received an error today that stated
"Some cross-sheet formulas can't be updated, because this sheet has more cells referenced from other sheets than the maximum allowed (100000)."
Its a pretty generic message that doesnt tell me which formula is not being updated nor which cross reference formula is causing me to hit this limit.
Does anyone know more about how this limit is calculated? My main sheet has about 250 rows. Each row has 14 columns that cross referenced from 5 other source sheets. I use the Index/Match formula for the cross referencing. That is only about 3,500 cross references which is under the 100,000 limit.
Does it matter how many rows are in the reference sheet to add to this calculation? Does it matter how many columns are in each of the source sheets?
Best Answer
-
You made the comment
"Do I need to factor in the field that is used in the 'Index/Match' formula so really I am referencing more fields that just the ones I am looking up?"
I assumed that your counts were only in reference to a single formula or function.
If you right click on any cell and select "Manage References", you should be able to see the total number of cross sheet references being used on that sheet.
How many are there in total, and what is the breakdown pointing to each of the other sheets? Is that what you have in your last comment? Are there any ranges that are covering more than one column?
Answers
-
The problem s not with your main sheet but with your 5 other source sheets being too large. All cross sheet references living on your main sheet combined reference more than 100,000 cells spread out across your 5 source sheets.
-
My 5 source Sheets:
Sheet 1, 372 rows, 1 cross reference column
Sheet 2, 255 rows, 3 cross referenced columns
Sheet 3, 283 rows, 2 cross referenced columns
Sheet 4, 31 rows, 3 cross referenced columns
Sheet 4, 14322 rows, 3 cross referenced columns
How does the calculation work to get to the 10000 cross referenced limit?
Do the non referenced columns in the source sheets matter? Should I eliminate them?
Do I need to factor in the field that is used in the 'Index/Match' formula so really I am referencing more fields that just the ones I am looking up?
Thanks!
-
Your numbers above come out to 44,762. If you have other cross sheet references then those will add to that as well.
Forgetting about which formula(s) each cross sheet reference is used in, exactly how many total do you have going to each of the 5 sheets?
-
Hi Paul, I dont fully understand your question. How many total of what?
Each row (currently 265 rows) in my main sheet cross reference:
Sheet 1, 1 field
Sheet 2, 4 fields (I mistakenly mentioned 3 fields prior)
Sheet 3, 2 fields
Sheet 4, 3 fields
Sheet 4, 3 fields
So that is a total of 13 cross reference fields for each row. Is that the number you were asking for?
-
You made the comment
"Do I need to factor in the field that is used in the 'Index/Match' formula so really I am referencing more fields that just the ones I am looking up?"
I assumed that your counts were only in reference to a single formula or function.
If you right click on any cell and select "Manage References", you should be able to see the total number of cross sheet references being used on that sheet.
How many are there in total, and what is the breakdown pointing to each of the other sheets? Is that what you have in your last comment? Are there any ranges that are covering more than one column?
-
I have a total of 17 references per the Reference Manager (thanks for letting me know about that tool).
Sheet 1 has 4 references
Sheet 2 has 4 references
Sheet 3 has 3 references
Sheet 4 has 4 references
Sheet 5 has 2 references
There are no ranges that cover more than 1 column.
-
Sheet 1, 372 rows, 4 references = 1488
Sheet 2, 255 rows, 4 references = 1020
Sheet 3, 283 rows, 3 references = 849
Sheet 4, 31 rows, 4 references = 124
Sheet 5, 14322 rows, 2 references = 28644
Grand Total: 32125
"Some cross-sheet formulas can't be updated, because this sheet has more cells referenced from other sheets than the maximum allowed (100000)."
Totaling up the inbound referenced cells gets us to 32,125. Does outbound references also count towards this limit? I can never remember.
-
Hiya!
I believe the 100,000 limit is just inbound references. There is a maximum total count of referenced cells in general, but that's a different number.
Is it possible that any of these 5 sheets happen to have many more rows than expected? For example, the one with 31 rows, what if there are rows further down in the sheet that aren't shown in first glance?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Paul and Genevieve for your answers. I now understand how this limit is calculated and how to see the true list of all the cross referenced fields. I found the main driver of where my count was going up fast and now can take some measures to help reduce it before I hit the limit.
-
@Genevieve Evans hi! I've got a group with the same issue. this one seems new, or at least the language has been updated. I think this is an error due to formulas on the impact sheets referencing too many cells in other sheets. However, the error actually says the opposite.
"...this sheet has more cells referenced from other sheets than the maximum allowed..."
This error is saying that other sheets are referencing cells on the impacted sheet too many times.
Can you confirm which direction this error is supposed to be pointing? @Paul Newcome as well.
-
Hi @Lucas Rayala ! I think you mean @Genevieve P. She's the real Smartsheet superstar for this community. :)
-
Hey @Lucas Rayala
This error is in relation to how many cells are being referenced by formulas in this sheet. This current sheet is the one taking the action of referencing, does that make sense?
I suppose I would personally adjust the wording of the error message to be along the lines of:
"...this sheet is referencing more cells than the maximum allowed..."
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. thanks for clarifying! do you happen to know if it matters how the target columns in the other sheet are ordered? for instance, if there are 1,000 rows in a target sheet, would it matter if I had the rows ordered so the values being looked up appeared at the top vs the bottom?
-
does this happen to you all day? :) thanks for tipping off Genevieve P!
-
The order doesn't matter since the entire column or range is selected; meaning that even if you're only bringing one cell back from the range, the formula still has to look into each individual cell (so each one is counted!) regardless of the sort order.
Do you have any VLOOKUP formulas on this sheet that could potentially be referencing more columns than needed? (E.g. columns between your lookup column and your column with the value to return... ones in the middle that can be left out). If so, I would recommend deleting that reference and creating INDEX(MATCH formulas (selecting each column individually) instead.
Here's an article that explains a bit more:
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
