IF(COUNTIF Returning #invalid ref
![Nancy Heater](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!