Formula suddenly not working

Options

I have a formula that I use across multiple sheets. It compares one cell entry (from a form) on sheet "A" against a cell on a master sheet and then fills out the cell contents on Sheet A based on master sheet info:

=INDEX({Hospital name}, MATCH([Hospital AU]@row, {AU}), 0)

That's the formula. It has been working for months... and then it has stopped, for no obvious reason and on all the sheets that use the formula.

Now, instead of reading the AU submitted in the form and pulling the Hospital Name from the master sheet based on the AU on the master sheet, it only returns the information from one AU (regardless of what is selected on the form and despite the fact that none of the options available on the form match the returned AU information.

Thoughts?

Answers

  • Julie Fortney
    Julie Fortney Overachievers
    Options

    @Gloydius I can offer some ideas based on previous experience, but the only way someone can say for sure is to have access to your sheets. My best suggestion is to submit a Smartsheet support ticket.

    Other things you could check in the meantime:

    • Confirm the cross-sheet references are still active. You can do so by right clicking in any cell on your sheet, then clicking Manage References.
    • Another issue I have run into is exceeding the cross-sheet reference limits. Do your sheets contain more rows/columns now than they did when the formula worked correctly? Smartsheet Support should be able to tell you if this is potentially the problem. And the good news is that these limits will increase substantially in the near future. This has been a big headache for us recently, so I can't wait.
  • Gloydius
    Options

    Thanks for these. It may be the latter, not sure - i'll submit a ticket.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!