Error - Formulas reference too many cells

cantpickname

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.

Genevieve P.

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

Paul Newcome

@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?

cantpickname

@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?

Paul Newcome

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.

Genevieve P.

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

schang

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?

Andrée Starå

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!

schang

@Andrée Starå I did not -- I moved them into an archive and limited the sheet to a certain range of time.

Andrée Starå

@schang

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!