Unique cells referenced in cross-sheet formulas Limit - How is this calculated?

scottreiter

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?

Paul Newcome

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?

Paul Newcome

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.

scottreiter

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!

Paul Newcome

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?

scottreiter

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?

Paul Newcome

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?

scottreiter

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.

Paul Newcome

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

@Genevieve P.

"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.

Genevieve P.

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?

scottreiter

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.

Lucas Rayala

@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.

_Genevieve Evans

Hi @Lucas Rayala ! I think you mean @Genevieve P. She's the real Smartsheet superstar for this community. :)

Genevieve P.

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

Lucas Rayala

@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?

Lucas Rayala

https://community.smartsheet.com/discussion/comment/371502#Comment_371502

does this happen to you all day? :) thanks for tipping off Genevieve P!

Genevieve P.

https://community.smartsheet.com/discussion/comment/371712#Comment_371712

Hi @Lucas Rayala

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:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-a-matching-value

Cheers,

Genevieve