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
-
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)
-
@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
-
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.
-
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)
-
@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)
-
@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
-
Thank you @Paul Newcome and @Kelly Drake the formula works! I appreciate both of your assistance!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!