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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!