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 intrasheet 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 insheet cells, columns, ranges, and crosssheet 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 = 10million)
Are any of your crosssheet 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
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 Crosssheet 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 insheet cells, columns, ranges, and crosssheet 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 = 10million)
Are any of your crosssheet 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

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 redoing 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
Check out the Formula Handbook template!