How do I avoid the cross sheet cell reference limit of 100,000?
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!
Answers
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives