markkeith
New Member
- Joined
- Sep 8, 2020
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
I'm newbie to VBA and have limited knowledge in making complex formulas in spreadsheet so I'm having difficulty thinking of a better approach to achieve my desired results.
I want each cell in multiple rows and columns to get a value of 1 or "" depending on the following logical test:
If Name and Date cell are blank then value is ""
If Employee is in Shift A then the cell in a row will get value of 1 from the start date up to certain numbers of cell depending of Work Rotation selected, employees in Shift B will start after the roster completion of Shift A.
Number of cell with 1 value will depends on rotation e.g. 3-3 Weeks (21 days on & 21 days off) or 6-2 Weeks (42 days on & 14 days off) and so on...
Also I want to get 0 in the 1st cell of days off, similar to the spreadsheet below.
I want each cell in multiple rows and columns to get a value of 1 or "" depending on the following logical test:
If Name and Date cell are blank then value is ""
=IF(OR(E$2="",$B3=""),"","more formula")If Employee is in Shift A then the cell in a row will get value of 1 from the start date up to certain numbers of cell depending of Work Rotation selected, employees in Shift B will start after the roster completion of Shift A.
Number of cell with 1 value will depends on rotation e.g. 3-3 Weeks (21 days on & 21 days off) or 6-2 Weeks (42 days on & 14 days off) and so on...
Also I want to get 0 in the 1st cell of days off, similar to the spreadsheet below.
| Auto Roster Sheet_rev2.xlsm | ||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | |||
| 1 | Week 32 | Week 33 | Week 34 | Week 35 | Week 36 | Week 37 | ||||||||||||||||||||||||||||||||||||||||||
| 2 | Name | Position | Shift | Sun, 14 Aug 2022 | Mon, 15 Aug 2022 | Tue, 16 Aug 2022 | Wed, 17 Aug 2022 | Thu, 18 Aug 2022 | Fri, 19 Aug 2022 | Sat, 20 Aug 2022 | Sun, 21 Aug 2022 | Mon, 22 Aug 2022 | Tue, 23 Aug 2022 | Wed, 24 Aug 2022 | Thu, 25 Aug 2022 | Fri, 26 Aug 2022 | Sat, 27 Aug 2022 | Sun, 28 Aug 2022 | Mon, 29 Aug 2022 | Tue, 30 Aug 2022 | Wed, 31 Aug 2022 | Thu, 1 Sep 2022 | Fri, 2 Sep 2022 | Sat, 3 Sep 2022 | Sun, 4 Sep 2022 | Mon, 5 Sep 2022 | Tue, 6 Sep 2022 | Wed, 7 Sep 2022 | Thu, 8 Sep 2022 | Fri, 9 Sep 2022 | Sat, 10 Sep 2022 | Sun, 11 Sep 2022 | Mon, 12 Sep 2022 | Tue, 13 Sep 2022 | Wed, 14 Sep 2022 | Thu, 15 Sep 2022 | Fri, 16 Sep 2022 | Sat, 17 Sep 2022 | Sun, 18 Sep 2022 | |||||||||
| 3 | 1 | Employee 1 | Supervisor | A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | |||||||||||||||
| 4 | 2 | Employee 2 | Tradesman | A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | |||||||||||||||
| 5 | 3 | Employee 3 | Tradesman | A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | |||||||||||||||
| 6 | 4 | Employee 4 | Supervisor | B | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||||||||||||||||||||||||||
| 7 | 5 | Employee 5 | Tradesman | B | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||||||||||||||||||||||||||
| 8 | 6 | Employee 6 | Tradesman | B | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||||||||||||||||||||||||||
| 9 | POB | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 0 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 0 | 3 | 3 | 3 | 3 | 3 | 3 | |||||
| 10 | ||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | Start Date | 14-Aug-22 | ||||||||||||||||||||||||||||||||||||||||||||||
| 12 | End of Year | 31-Dec-22 | ||||||||||||||||||||||||||||||||||||||||||||||
| 13 | Days | 140 | ||||||||||||||||||||||||||||||||||||||||||||||
| 14 | Work Rotation | 4-4 Weeks | ||||||||||||||||||||||||||||||||||||||||||||||
| 15 | ||||||||||||||||||||||||||||||||||||||||||||||||
Sheet1 | ||||||||||||||||||||||||||||||||||||||||||||||||
