Hi All
I need some help brainstorming the most efficient way to pull data from my scheduling workbook. Unfortunately, I don't have the option of changing the layout, but what I need to do is pull data on how many of each shift each person has worked over time. So essentially, columns B to E.
At the end of the year I'd like to be able to see how many of each start and finish time each person has worked and on what date. I currently have a dashboard created with a filter function that allows me to filter by person and see start/end times, but I'd like to be able to pull the whole group at once, as I want to use this data for other things.
Each worksheet like the example below is 7 days, so the workbook will eventually contain 52 sheets (one for each week of the year). Column C is actually the workers names in the real version.
Workers may not necessarily be on the same start/end time every day of the week so it's no use for me to just pull data from the first day of the week, I need all 7 days days from each sheet.
The example is significantly reduced too, so I'd rather not have to remove any of the spaces between days as the live version of this document is pretty busy to look at as it is, although I appreciate the spaces may complicate things?
Would really appreciate your ideas. Thanks in advance.
I need some help brainstorming the most efficient way to pull data from my scheduling workbook. Unfortunately, I don't have the option of changing the layout, but what I need to do is pull data on how many of each shift each person has worked over time. So essentially, columns B to E.
At the end of the year I'd like to be able to see how many of each start and finish time each person has worked and on what date. I currently have a dashboard created with a filter function that allows me to filter by person and see start/end times, but I'd like to be able to pull the whole group at once, as I want to use this data for other things.
Each worksheet like the example below is 7 days, so the workbook will eventually contain 52 sheets (one for each week of the year). Column C is actually the workers names in the real version.
Workers may not necessarily be on the same start/end time every day of the week so it's no use for me to just pull data from the first day of the week, I need all 7 days days from each sheet.
The example is significantly reduced too, so I'd rather not have to remove any of the spaces between days as the live version of this document is pretty busy to look at as it is, although I appreciate the spaces may complicate things?
Would really appreciate your ideas. Thanks in advance.
| Example Schedule.xlsx | |||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 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 | |||
| 2 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | 7:00 | 8:00 | 9:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | ||||||||
| 3 | Start Time | End Time | 23:00 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | 7:00 | 8:00 | 9:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | ||||||
| 4 | mi | 03/04 | Shift 1 | 6:00 | 12:00 | Shift 1 | Shift 1 | Shift 1 | Shift 1 | Shift 1 | Shift 1 | ||||||||||||||||||||||||||||
| 5 | mi | 03/04 | Shift 2 | 15:00 | 19:00 | Shift 2 | Shift 2 | Shift 2 | Shift 2 | ||||||||||||||||||||||||||||||
| 6 | mi | 03/04 | Shift 3 | ||||||||||||||||||||||||||||||||||||
| 7 | mi | 03/04 | Shift 4 | 22:00 | 1:00 | Shift 4 | Shift 4 | Shift 4 | |||||||||||||||||||||||||||||||
| 8 | mi | 03/04 | Shift 5 | 6:00 | 10:00 | Shift 5 | Shift 5 | Shift 5 | Shift 5 | ||||||||||||||||||||||||||||||
| 9 | |||||||||||||||||||||||||||||||||||||||
| 10 | ###### | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | 7:00 | 8:00 | 9:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | |||||||
| 11 | Start Time | End Time | 23:00 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | 7:00 | 8:00 | 9:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | ||||||
| 12 | ju | 04/04 | Shift 1 | 6:00 | 12:00 | Shift 1 | Shift 1 | Shift 1 | Shift 1 | Shift 1 | Shift 1 | ||||||||||||||||||||||||||||
| 13 | ju | 04/04 | Shift 2 | 15:00 | 19:00 | Shift 2 | Shift 2 | Shift 2 | Shift 2 | ||||||||||||||||||||||||||||||
| 14 | ju | 04/04 | Shift 3 | 8:00 | 14:00 | Shift 3 | Shift 3 | Shift 3 | Shift 3 | Shift 3 | Shift 3 | ||||||||||||||||||||||||||||
| 15 | ju | 04/04 | Shift 4 | 23:00 | 2:00 | Shift 4 | Shift 4 | Shift 4 | Shift 4 | ||||||||||||||||||||||||||||||
| 16 | ju | 04/04 | Shift 5 | ||||||||||||||||||||||||||||||||||||
Schedule 1 | |||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A4:A8 | A4 | =TEXT($A$1,"ddd") |
| B4:B8 | B4 | =$A$1 |
| E4:E8,E12:E16 | E4 | =IF($D4<>"",XLOOKUP($C4,'Show Info & Data Validation'!$A$2:$A$11,'Show Info & Data Validation'!$B$2:$B$11)+$D4,"") |
| F4:AC8,AE12:AK16,AE4:AK8 | F4 | =IF($D4="","",IF(AND(F$2>=TRUNC($D4,10),F$2<TRUNC($E4,10)),$C4,"")) |
| A10 | A10 | =$A$1+1 |
| A12:A16 | A12 | =TEXT($A$10,"ddd") |
| B12:B16 | B12 | =$A$10 |
| F12:AC16 | F12 | =IF($D12="",IF(AE4<>"",AE4,""),IF(AE4<>"",AE4,"")&(IF($D12="","",IF(AND(F$2>=TRUNC($D12,10),F$2<TRUNC($E12,10)),$C12,"")))) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D4:D8 | List | ='Show Info & Data Validation'!$D$2:$D$25 |
| D12:D16 | List | ='Show Info & Data Validation'!$D$2:$D$25 |
