Unmark a checkbox if any instance of another sheet's checkbox is marked

celtics345

Got a knotty question that I'm sure the savant's here can solve, as I am a novice at column formulas.

The issue? We'd like to track average hours worked across multiple employees, however we don't want the employees who are currently training to dramatically offset the data. Thus, we'd like to check a box indicating that this person is training, and therefore their data should not be counted. This latter aspect we've already solved. We're just looking to automate the checking of the box.

This checkbox would reference a source sheet (Training Grid), and the checkbox and TraineeContact column contained therein. The box in question (Cleared) is only selected when someone concludes their training, thus when it's checked, it would trigger the destination sheet's (Trainee Totals) checkbox to de-check itself. This is regardless of the fact if there are other checkboxes attached to that same trainee that are unchecked, as this Cleared box will only be checked once in a constantly-updating sheet. Meaning, if any instance of Cleared is checked for that trainee, it would de-check the destination sheet's box.

I'm currently working with the formula below, to no avail. Likely due to the fact that it's struggling to register that there are other un-checked boxes in the same column for the same trainee. Any help would be appreciated!

=IF(COUNTIFS({Cleared},1,{TraineeContact},[Trainer]@row)>0,1,0)

Paul Newcome

The text in your Trainee column does not match the text in your [Trainee Contact] column.

Paul Newcome

That formula looks like it should be working. Are you able to provide screenshots for reference?

celtics345

Of course, thanks for helping!

Shown above is the source sheet (Training Grid). I've just blacked out last names, but as you can see the Cleared button has been selected for Connor and Michael, however Robert is still currently training. Note that there are previous dates of Connor's where he has not been considered Cleared yet.

This is the destination sheet (Trainee Totals), with the current formula. It doesn't give me any errors. Connor and Robert both have the formula, both boxes are unchecked. I'd like for Robert to be checked, as his training is still on-going (as denoted by the lack of a Cleared button check in the source sheet). Changing the '1' to a '0' after Cleared in the formula doesn't change anything, if it would?

Paul Newcome

The text in your Trainee column does not match the text in your [Trainee Contact] column.

celtics345

Ugh. I hate when it's something so simple. Thank you Paul, I appreciate it!

Paul Newcome

Happy to help. 👍️