# Error - Formulas reference too many cells

Options
✭✭✭✭

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.

Options

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.

Cheers,

Genevieve

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

Options

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.

Cheers,

Genevieve

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!