#REF errors on existing sheets that previously worked

Options

Hi,

I've been getting #REF errors over the last week on existing sheets with correct references.

When I look at the reference in the formula it's pointing to the correct column.

I've found a fix where we have to edit the reference to another column. Then change it back to the correct one. After that the #REF error goes away.

Does anyone have any idea what could be causing this. My guess is that it's on the Smartsheet side as the sheet and reference worked previously. And editing the reference away from the correct column and back again fixes it.

Thanks

Answers

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

    Hi @veneer

    I hope you're well and safe!

    Is it a busy sheet with many cells and cross-sheet formulas?

    Are you using VLOOKUP or INDEX/MATCH formulas?

    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.

  • veneer
    veneer ✭✭
    Options

    Hi @Andrée Starå,

    I'm good thanks :)

    I had quite a few #REF errors using INDEX/MATCH referencing a relatively busy sheet. The referenced sheet has some columns that are also references.

    The most recent one I had today was a very basic sheet referencing another simple sheet.

    Over the last week there's been a few odd things going on. Also had a few #BLOCKED errors. I simply go into the sheet they resolve themselves and I resave the sheet.

    All these were existing sheets and references that had worked for months.

    Thanks

  • Andrew Cotter
    Options

    I am encountering a similar issue. Busy sheets, lots of references but no issues until this afternoon. Now getting lots of #REF errors on columns where no changes have been made. Formulas look correct too...

  • Andrew Cotter
    Options

    I tried the solution listed above... referencing a different column and then changing it back. That seems to work but is still quite annoying and will add a great deal of work changing and reverting all those references... hopefully it fixes itself overnight! Cmon Smartsheet, you can do it!

  • veneer
    veneer ✭✭
    Options

    @Andrew Cotter I logged a support case and they have acknowledged it's an issue on their side.

    "This is a known issue and our Engineering Team is working on resolving it. "

    Would be nice to see it on the https://status.smartsheet.com/ page though.

  • Daniell Hepting
    Options

    Thanks, @Andrew Cotter , I am having the same issues at my company where we are getting #REF errors that we can't resolve. Good to know it's something they are working to fix.

  • BFuller
    BFuller ✭✭✭
    Options

    I am having the same issue. I've contacted support because these sheets/dashboards are working one minute and the next they are #REF. Support says it's some kind of bug and has escalated my inquiry, but I'm still waiting. Right now, it's a guess if when I bring up my dashboard if I will have the data or #REF across the whole thing.

  • BFuller
    BFuller ✭✭✭
    Options

    I have noticed the #REF error occurs when someone updates via a form the master sheet.

  • mphi
    Options

    Hi, this just happened to me this morning PST. Is there an update on this?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!