How do I avoid the cross sheet cell reference limit of 100,000?

Kenneth Osborne
edited 05/20/21 in Smartsheet Basics

I have several formulas that are critical to one of my dashboards. They reference a master sheet that grows daily in number of rows. I end up getting the cross sheet cell reference limit warning about every 15 days. Is there some way to easily mitigate this? I'd like to avoid reducing the size of the master sheet so frequently if possible.

Thank you!


  • Matthew Flebbe
    Matthew Flebbe ✭✭✭✭
    edited 05/20/21

    Hi Kenneth,

    Do you have a key value in your master sheet that you can reference using INDEX and MATCH?

    For example, I use a master sheet to look up product descriptions by indexing the description then comparing the barcode value using a formula like this:

    =INDEX({Description}, MATCH(SCAN@row, {Barcode Text}), 0)

    The formula has two external references to my master sheet (Description and Barcode Text)

    The formula first indexes the "Description" column of all rows on the master sheet: =INDEX({Description},

    Then it matches the barcode scanned into the cell value on a given line against that externally referenced index: MATCH(SCAN@row, {Barcode Text}), 0) and returns the value of the Description in the row that has the same text in the "Barcode Text" column on my master sheet.

    Using an indexing formula with a key value would negate the need to use hard cell to cell links between sheets.

    If this doesn't solve your problem please provide more information to clarify and possibly some screenshots so I can better understand. Data Shuttle (a premium Smartsheet add-on) may also be an elegant solution to consider if the above does not work.



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

    Hi @Matthew Flebbe

    I hope you're well and safe!

    What formulas are you using now? How many different references?

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

    I hope that helps!

    Be safe and have a fantastic day!


    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!


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

    W: | | P: +46 (0) - 72 - 510 99 35

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