100000 cell reference limit

Michael Culley

I reached the 100000 cell reference limit so I deleted some references and it's still showing that error. I should definitely have room for new references now. How do I fix this?

Genevieve P.

Hi @Michael Culley

Can I clarify how you deleted the references? Did you delete out a {cross sheet reference} from within a formula, or did you use the Sheet Reference Manager?

I would first check the Sheet Reference Manager to see if there are any unused references I could fully delete from the sheet.

Then I would replace any instance of a VLOOKUP with an INDEX(MATCH formula instead, in case that helps eliminate extra referenced cells in the Vlookup range.

Cheers,

Genevieve

taylormckay

Hi @Genevieve P. ,

I'm hoping you can help shed some more tips on how to work within this 100,000 reference cell limit.

If I have 500 formula rows in my Main Sheet, and in those cells I INDEX/MATCH a larger sheet with 5,000 rows for specific information. I'm now using 5,000,000 cells? (500 x 5000 x 2 columns).

I can't really fathoming integrating my datasets well with such a small reference limit. Is there a better way to organize my 5000 row sheet perhaps?

Thanks!

Genevieve P.

Hi @taylormckay

If you're using the exact same INDEX(MATCH formula in all 500 rows, then you're referencing the same cells in your {references}, so they only count once:

5,000 rows x 2 columns = 10,000 cell references

Here's another Community thread that discusses some best practices when working with cell limits:

https://community.smartsheet.com/discussion/78708/100-000-cell-reference-limit-do-i-understand-it-right-and-can-i-monitor-it

Cheers,

Genevieve