Formula for a how to check a check box based on two ranges in two sheets
Hello,
Can someone please help me with a formula that will check a box when it finds the email in both sheet.
Master sheet has the Email Address and the box that needs to be check
if it matches an email address in the BUP Survey sheet
Best Answer
-
Hi @ealexis,
please try the following:
1- Create a Helper Column in the Master Sheet: This column will be used to search for the email address in the "BUP Survey" sheet and return a specific value if the email is found. You can name this column something like "Email Found in BUP Survey".
2-Use a CROSS-SHEET VLOOKUP Formula: In the "Email Found in BUP Survey" helper column, you would use a VLOOKUP formula to search for the email address in the "BUP Survey" sheet. Since direct cross-sheet checkbox toggling isn't possible, this formula will return a value indicating the presence of the email address.
Assuming that the "BUP Survey" sheet has an "Email Address" column as well, your formula in the helper column on the "Master" sheet might look something like this:
=IFERROR(VLOOKUP([Email Address]@row, {BUP Survey Email Address Range}, 1, false), "Not Found")
3- for check box column called [Proof Of BUP Survey] use the following formula to check the box if the email is founded
=IF([Email Found in BUP Survey]@row = "Not Found", FALSE, TRUE)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @ealexis,
please try the following:
1- Create a Helper Column in the Master Sheet: This column will be used to search for the email address in the "BUP Survey" sheet and return a specific value if the email is found. You can name this column something like "Email Found in BUP Survey".
2-Use a CROSS-SHEET VLOOKUP Formula: In the "Email Found in BUP Survey" helper column, you would use a VLOOKUP formula to search for the email address in the "BUP Survey" sheet. Since direct cross-sheet checkbox toggling isn't possible, this formula will return a value indicating the presence of the email address.
Assuming that the "BUP Survey" sheet has an "Email Address" column as well, your formula in the helper column on the "Master" sheet might look something like this:
=IFERROR(VLOOKUP([Email Address]@row, {BUP Survey Email Address Range}, 1, false), "Not Found")
3- for check box column called [Proof Of BUP Survey] use the following formula to check the box if the email is founded
=IF([Email Found in BUP Survey]@row = "Not Found", FALSE, TRUE)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
thank you so much! that did the trick
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 151 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!