IF(COUNTIF Returning #invalid ref
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!
Answers
-
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.
-
Have you made sure that the new cross sheet references are in fact being created properly?
-
@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.
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!