# Formula Help

Options

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!

• ✭✭✭✭✭✭
Options

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)

• Overachievers Alumni
Options

@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

• Options

I should have said, that I want the box to put a check in automatically when the staff submit their schedules for a particular week.

• ✭✭✭✭✭✭
Options

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)

• Options

@Paul Newcome It comes back with an Incorrect Argument Set. Here is what I typed in:

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

• Overachievers Alumni
Options

@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

• Options

Thank you @Paul Newcome and @Kelly Drake the formula works! I appreciate both of your assistance!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!