IF(COUNTIF Returning #invalid ref

Nancy Heater

Hello smartsheet community!

We are using the following formula to look up data across multiple sheets to see if the client ID exists elsewhere. I can add in the first (6) sheers to reference and it works just fine. When I start to add the remaining (3) sheets, it breaks.

I also tired to remove one of the current references to a known 'working' sheet, and add in one of the remaining (3) sheets and it also breaks.

All of the sheets columns we are referencing are formatted correctly, and this same formula is working correctly in other sheets. I am stumped.

IF(COUNTIF({sheet 2023}, =ID@row) + COUNTIF({sheet 2022}, =ID@row) + COUNTIF({sheet2021}, =ID@row) + COUNTIF({sheet 2020}, =ID@row) + COUNTIF({sheet 2019}, =ID@row)> 0, 1, 0)

Thanks in advance for the assist!

Eric Law

Hello @Nancy Heater,

Invalid Ref means that one of your reference"{}" is not correct. Try testing each references individually to see if it works. Here is the source.

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.219291482.211356122.1679423845-187287282.1664569207&_gl=1*10dhzs3*_ga*MTg3Mjg3MjgyLjE2NjQ1NjkyMDc.*_ga_ZYH7XNXMZK*MTY3OTU5ODIwNy40NS4xLjE2Nzk2MDE1NjcuNTQuMC4w#invalidref

Paul Newcome

Have you made sure that the new cross sheet references are in fact being created properly?

Nancy Heater

@Paul Newcome @Eric Law - We are adding each reference individually, and checked all of the specifics of the formulas, and everything appears as it should. Even if I remove everything else and try and add in just the (3) that I'm missing, it won't work. Its almost as if Smartsheet doesn't think the link can be made, even though its making the same link/reference on other sheets.

Genevieve P.

Hey @Nancy Heater

Is there any chance that one of your referenced columns has a formula error in it, even in one cell? This will then bubble up into other formulas.

Try adding an IFERROR around the formulas in your other sheets to see if that helps. 🙂