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

Tana V
Tana V ✭✭✭✭
edited 08/04/23 in Smartsheet Basics

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.


Best Answer