Checkbox cross referencing person on another sheet

Linda F

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.

Heather Duff

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 Duff

@Linda F

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 Duff

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

Linda F

@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)

Heather Duff

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.

Linda F

@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)

Heather Duff

@Linda F

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.

Linda F

@Heather D This one worked as well. 2 different ways of doing it - that is awesome ! Thank you so much for your help!

Heather Duff

😊 Happy to help!