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

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!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!