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 help? 👀 | 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 help? 👀 | 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
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!