Checkbox cross referencing person on another sheet
I am looking for a formula to cross check another sheet to check a box on my cumulative sheet.
Example, someone completes a required training on EM# 1 sheet then on the EM# 2 sheet I want that persons name to be checked from a list of other individuals. This is the formula I used,
=IF({Emergency Preparedness Employee Acknowledg Range 1}, HAS(@cell, "Doe, John"), 1, 0)
Any help would be appreciated.
Best Answers
-
Hi @Linda F
One last try of the countif formula:
=IF(COUNTIF({Emergency Preparedness Employee Acknowledg Range 3}, [Assign to]@row) > 0, 1, 0)
This is assuming your "Assign To" column is listing the employee names you want to check for on the other sheet.
-
I was able to get it to work with a few tweaks:
=IFERROR(IF(INDEX({Emergency Preparedness Employee Acknowledg Range 3}, MATCH([Assign to]@row, {Emergency Preparedness Employee Acknowledg Range 3}, 0)) <> "", 1, 0), 0)
The iferror and ending ,0 are added because it was displaying #NO MATCH instead of unchecked.
Answers
-
Hi @Linda F ,
Try this:
=IF(COUNTIF({Emergency Preparedness Employee Acknowledg Range 1}, @cell="Doe, John")>0, 1, 0)
Let me know if it works!
Best,
Heather
-
@Heather D It didn't work. The whole column now has a check mark in each cell. I was working with the below formula as IF, Index. It works but it did the opposite of what you suggested. No cells are checked now.
=IF(INDEX({Emergency Preparedness Employee Acknowledg Range 3}, MATCH([Assign To]@row, [Assign To]@row:[Assign To]@row, 0)) <> "", 0, 1)
-
Hi @Linda F
One last try of the countif formula:
=IF(COUNTIF({Emergency Preparedness Employee Acknowledg Range 3}, [Assign to]@row) > 0, 1, 0)
This is assuming your "Assign To" column is listing the employee names you want to check for on the other sheet.
-
@Heather D IT WORKED !!! Thank you.
Out of curiosity, would the below also work and if so, what did I do wrong?
=IF(INDEX({Emergency Preparedness Employee Acknowledg Range 3}, MATCH([Assign To]@row, [Assign To]@row:[Assign To]@row, 0)) <> "", 0, 1)
-
I was able to get it to work with a few tweaks:
=IFERROR(IF(INDEX({Emergency Preparedness Employee Acknowledg Range 3}, MATCH([Assign to]@row, {Emergency Preparedness Employee Acknowledg Range 3}, 0)) <> "", 1, 0), 0)
The iferror and ending ,0 are added because it was displaying #NO MATCH instead of unchecked.
-
@Heather D This one worked as well. 2 different ways of doing it - that is awesome ! Thank you so much for your help!
-
😊 Happy to help!
Help Article Resources
Categories
Check out the Formula Handbook template!
