What's the best way to pull data from this sheet?

[eraust]

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.

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

 

[bobaol]

Hello eraust, have you tried to do your calculations and manipulations in columns BA (or whatever open columns you have to the right)? or do the calculations in rows 1001 and below. While you are not able to change the layout and design of the sheet, you can go to the empty areas to do your calculations (i assume). If you go to to right, or to the bottom, other people will never see your work, but you will know what it is used for. I would do the calculations beginning in row 1001, and manipulate the data in that area until you have the answer. once you have the answer, you can repeat it for those 52 sheets, then have a single summary sheet to consolidate all the 52 single sheets. hope this helps. cheers!