Reference Formula Across Sheets
Hello,
I have been working with this formula below in order to search multiple sheets to see if a name has been submitted before. The formula is currently in a checkbox format and I keep getting the error "Boolean Expected". I think it wants to display a number but what I want it to do is check the box if it has appeared on any of the other sheets.
=IF(COUNTIF({Sample Range 1}, [Name Field]@row) > 0, 1, 0) + IF(COUNTIF({Sample Range 2}, [Name Field]@row) > 0, 1, 0) + IF(COUNTIF({Sample Range 3}, [Name Field]@row) > 0, 1, 0)
Any suggestions?
Answers
-
The error is coming through because you're adding together the IF statements. This means that if all three are correct, the output is actually 3 (1 + 1 + 1) instead of 1.
Try adding all the separate COUNTIFS together first, then you can do one large overall IF statement to say if the total of all these sheets is greater than 0, check the box, like so:
=IF(COUNTIF({Sample Range 1}, [Name Field]@row) + COUNTIF({Sample Range 2}, [Name Field]@row) + COUNTIF({Sample Range 3}, [Name Field]@row)) > 0, 1, 0)
Does that make sense?
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Thank you, it worked perfectly! This was very helpful!!
-
No problem at all! I'm glad I could help.
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!