How do I avoid the cross sheet cell reference limit of 100,000?

Kenneth Osborne

I have several formulas that are critical to one of my dashboards. They reference a master sheet that grows daily in number of rows. I end up getting the cross sheet cell reference limit warning about every 15 days. Is there some way to easily mitigate this? I'd like to avoid reducing the size of the master sheet so frequently if possible.

Thank you!

Matthew Flebbe

Hi Kenneth,

Do you have a key value in your master sheet that you can reference using INDEX and MATCH?

For example, I use a master sheet to look up product descriptions by indexing the description then comparing the barcode value using a formula like this:

=INDEX({Description}, MATCH(SCAN@row, {Barcode Text}), 0)

The formula has two external references to my master sheet (Description and Barcode Text)

The formula first indexes the "Description" column of all rows on the master sheet: =INDEX({Description},

Then it matches the barcode scanned into the cell value on a given line against that externally referenced index: MATCH(SCAN@row, {Barcode Text}), 0) and returns the value of the Description in the row that has the same text in the "Barcode Text" column on my master sheet.

Using an indexing formula with a key value would negate the need to use hard cell to cell links between sheets.

If this doesn't solve your problem please provide more information to clarify and possibly some screenshots so I can better understand. Data Shuttle (a premium Smartsheet add-on) may also be an elegant solution to consider if the above does not work.

Thanks,

Matt

Andrée Starå

Hi @Matthew Flebbe

I hope you're well and safe!

What formulas are you using now? How many different references?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic day!

Best,

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

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