Too many cells referenced by formulas error

I'm receiving the following error, trying to use an Index/Match for a cross-sheet formula: "There are too many cells referenced by formulas in this sheet. Please reduce the number of cells referenced by formulas."  

In the source sheet I have 21 columns (11 columns with cross-sheet formulas) containing 16,000 cells with formulas (784 rows currently, but this will be increasing). The sheet has 27 total range references (per Manage References).  

Why would I receive this error when the limit is 100,000 cells inbound cells referenced from other sheets?

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Julie Nelson

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots of the sheets and reference manager? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Julie Nelson
    Julie Nelson ✭✭✭✭

    According to Smartsheet Support, there is currently a bug with cross-sheet formulas. After I made the initial post above, the error message continued even when using an Index/Match with even when the source and destination sheets were extremely basic (only a couple of columns with formulas). The error finally stopped after a few hours. If you run into the same issue, call Smartsheet Support, as they may need to escalate within their team.

  • Hi Smartsheets,

    I am also having the same issue.

    When I try and copy a row and paste it to a new row then click save, it gives me the below error. I have admin access. My work colleagues have Editor access and have the same issue. We can't add any new info.


    Please help.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @CraigR

    I am adding the same answer here for visibility.

    It seems like you're hitting one of the limits.

    • How many columns/rows do you have, and is a formula heave sheet?
    • Do you have many cross-sheet references?

    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 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:[email protected] | 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!