Error - Formulas reference too many cells
I have an automation (copy row to sheet B) that is failing. My error is attached. I investigate Sheet B, which is a large sheet, many complicated formulas, but my total count of cross sheet references is only 14.
Does this mean there is a limit for the amount of intra-sheet cell references? If so, what is the limit, how can I see how close I am to that limit, etc.
Best Answer
-
Hi @cantpickname,
The total count of cells referenced by formulas in a sheet cannot exceed 25 million. This includes references to in-sheet cells, columns, ranges, and cross-sheet references.
To understand how this limit is calculated:
If a single cell has a formula that refers to 10 columns on a sheet with 10,000 rows, then that formula is referencing 100,000 cells (10columns x 10,000rows = 100,000cells).
However, if that formula exists in 100 different cells, then the total referred cells is 10,000,000 (100,000 referred cells * 100 formulas = 10-million)
Are any of your cross-sheet formulas VLOOKUP? If so, I would suggest replacing these with INDEX(MATCH functions instead. VLOOKUP will refer to an entire table range, while INDEX(MATCH only refers to the columns necessary to lookup and return a value.
Let me know if you need help adjusting your formulas!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
@Genevieve P and @Andrée Starå I haven't seen this error before. Any thoughts?
@cantpickname You have 14 cross sheet references, but how many total cells are they referencing?
-
@Paul Newcome 1687. Also, the error message didn't appear when I open the sheet at the top (like the Cross-sheet reference error) this one appeared when I was editing the workflow.
7 x 209 = 1,463
3x 64 = 192
4x 8 = 32
It's hitting some limit, it just doesn't provide any information on where I need to adjust my formulas. Internal within the sheet, or I have other sheets that pull data from this Sheet B, is that too many?
-
I've never heard of a limit like this before, but that doesn't mean it isn't there. I suggest reaching out to Support to see if they can provide an insight, and in the meantime maybe someone else here in the Community can help.
-
Hi @cantpickname,
The total count of cells referenced by formulas in a sheet cannot exceed 25 million. This includes references to in-sheet cells, columns, ranges, and cross-sheet references.
To understand how this limit is calculated:
If a single cell has a formula that refers to 10 columns on a sheet with 10,000 rows, then that formula is referencing 100,000 cells (10columns x 10,000rows = 100,000cells).
However, if that formula exists in 100 different cells, then the total referred cells is 10,000,000 (100,000 referred cells * 100 formulas = 10-million)
Are any of your cross-sheet formulas VLOOKUP? If so, I would suggest replacing these with INDEX(MATCH functions instead. VLOOKUP will refer to an entire table range, while INDEX(MATCH only refers to the columns necessary to lookup and return a value.
Let me know if you need help adjusting your formulas!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Hi @Genevieve P. I have this exact issue and have adjusted all my vlookup formulas to use index(match), understanding that this reduces the processing needs significantly, but I am still stuck with this error. Is there any chance you could help with looking at some of my formulas without completely re-doing the workflow?
-
Hi @schang
I hope you're well and safe!
Did you delete the old ranges?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.
-
@Andrée Starå I did not -- I moved them into an archive and limited the sheet to a certain range of time.
-
I'd recommend testing to remove the unused references and see if you have the same issue after an hour.
Did that work/help?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!