Formula Help

My ultimate goal is to have a checkbox checked automatically.

I have two sheets...(Employee Staff Schedules) and (Schedule Compliance)

Each Friday employees provide their schedules through an Employee Staff Schedules Form. The Form includes the Employee Name, two date fields (Week Start Date) and (Week End Date) and then each day of the week of what shift they are working.

The Schedule Compliance Sheet contains the Employee Name and a Checkbox Column for every week.

I have been trying to Index/Match, but I keep getting an error. Come someone please provide me with a formula? Thank you!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are going to want to use a COUNTIFS inside of an IF statement saying that if the count of rows for that employee with that particular week is greater than zero then check the box, and you are going to have to modify the formula for each week since we will have to "hard code" each date in.

    =IF(COUNTIFS({Other Sheet Name Column}, @cell = [Employee Name]@row, {Other Sheet Week Start Column}, @cell = DATE(2021, 05, 16)) > 0, 1)

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    Answer ✓

    @Ginny Shoemaker you have an extra segment in there.

    =IF(COUNTIFS({Employee Name}, @cell = [Employee Name]@row, {Week Start Date}, @cell = DATE(2021, 5, 30)) > 0, 1)

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!