How can I update a checkbox on a sheet based off of criteria from another sheet?

Tana V

I'm trying to update a checkbox on a sheet based off of criteria from another sheet. I've seen several examples using the INDEX / MATCH function but haven't had any luck because the situations seem to be a little different than mine.

Here is my situation:

Sheet 1: Time Tracking - updated by each employee each week via a form;

Columns in play: Employee ID, Employee Name, Week Starting (date) = dropdown list with all week of dates for 2023. This sheet only includes the employees that DID enter time for the week.

Sheet 2: Time Tracking - Validation by Week - has an entry for ALL employees and checkboxes for each of the week of dates.

Columns in play: Employee ID, Employee Name, a checkbox column for each weeks entry (7/10, 7/17, 7/24, etc.)

My goal is the have the week of date checkbox (ex; 0710) update IF a record is found in the Time Tracking sheet with a matching Employee ID and Week Of date so I can then filter out what is not checked for a specific week to know the employees that DIDN'T enter their time.

If the Time Tracking sheet (Sheet 1) has a record for Employee 1234567 for Week of 7/10/2023 then update the Validation Sheet (Sheet 2) with a checkmark for the 7/10 column for that employee.

Validation Sheet (2): (WO = week of / checkboxes)

Time Entry Sheet (1): (Week Starting = WO in sheet 2)

Thank you in advance!!

Edit: I also have a column in Sheet 1 that joins the EmpID+EmpName+WeekStart in case that can be used. I could create the same thing in Sheet 2 but I don't have the Week Start so doesn't seem to be helpful.

Paul Newcome

You would use an IF/COUNTIFS combo to count how many rows have the employee ID and the Week Starting. If that count is greater than zero (meaning there was at least one entry), check the box.

=IF(COUNTIFS({Source Sheet EMP ID}, @cell = [Employee ID]@row, {Source Sheet Week Starting}, @cell = "7/10/2023")> 0, 1)

Paul Newcome

You would use an IF/COUNTIFS combo to count how many rows have the employee ID and the Week Starting. If that count is greater than zero (meaning there was at least one entry), check the box.

=IF(COUNTIFS({Source Sheet EMP ID}, @cell = [Employee ID]@row, {Source Sheet Week Starting}, @cell = "7/10/2023")> 0, 1)

Tana V

@Paul Newcome THANK YOU!!!! That works perfect!!!

Paul Newcome

Happy to help. 👍️