Uncheck cell based on values in another sheet

H.Colo

Hi all, I have a workflow request. I am trying to get a checkbox to reset to unchecked based on a series of conditions in another sheet. The below screenshot is the first sheet in the workflow.

In this example, the checkbox in the column “Needs Review” needs to be unchecked based on these conditions:

1.      A Review Date (in another sheet) is in the current year

2.      A Review Date (in another sheet) is in the past

3.      The Employee Name in the first sheet matches the Employee Name in the master sheet. This is so the box doesn’t untick any time the review date is in the past/review year is current; it is meant to check for that individual only.

In short, the checkbox needs to untick if an individual has had a review in the current year, the review date is in the past, and the individual name matches on both sheets.

Please let me know what additional questions you may have on this or if this request makes sense at all. Thank you in advance for your time!

Jeff Reisman

@H.Colo ,

you will need to add hidden helper columns to the sheet where you want automation to uncheck the box. Use INDEX/MATCH to collect the values from the other sheet and bring them into those new columns.

Set your automation to be triggered when a row changes, and add a condition block to specify the values which should cause the Action of unchecking the box.

H.Colo

Thanks for the quick response Jeff! I'm struggling to combine/utilize these in a way where they search up and down an entire column for a string. I'm trying to use this formula:

=MATCH([Employee Name]@row, {IN_PROGRESS_Succession Planning Master She Range 2})

I selected the entire column with employee names in the separate Succession Master Planning Sheet, but it only returns a match if I select one cell with the name in it.

Thanks again for your help, any advice is appreciated!