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!

Find more posts tagged with

Accepted answers

Paul Newcome

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

@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)

All comments

Ginny Shoemaker

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.

Paul Newcome

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

Ginny Shoemaker

@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)

Kelly Drake

@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)

Ginny Shoemaker

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

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.