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.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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?

  • cantpickname
    cantpickname ✭✭✭✭
    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?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    schang ✭✭
    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?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    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.

  • schang
    schang ✭✭
    Options

    @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å
    Andrée Starå ✭✭✭✭✭✭
    Options

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

    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!