Improve Holiday Planner by using named ranges in an array?

[mrbenn]

I have a holiday planner to work with - not mine originally but the job do not want to make any significant changes (all "used to this one")

In the range I have pasted below there are "Months" defined by merged cells. In the second xl2bb paste is the section that shows the formulas being used to lookup. In the event that they year changes or another person is added the formulas need re-examined or re-done completely. My idea is to have named ranges so that the formula can refer to the name and the named range and it will do the calculations based on that. It would mean that all I would need to change would be the definition of the named range each time? I don't know if this is even possible but I would love an expert to have a look and give their opinion. I can upload the full spreadsheet to Onedrive etc if anyone needs

2022 MALLUSK HOLIDAY PLANNER.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	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL
1	Click on this row on the current month you are working on this shows the payroll month and not the calendar month	MALLUSK 2022	JANUARY																																				FEBRUARY																								MARCH																														APRIL
2	Employee name	Start/Leaver Date	Thursday 09 December	Friday 10 December	Saturday 11 December	Monday 13 December	Tuesday 14 December	Wednesday 15 December	Thursday 16 December	Friday 17 December	Saturday 18 December	Monday 20 December	Tuesday 21 December	Wednesday 22 December	Thursday 23 December	Friday 24 December	Saturday 25 December	Monday 27 December	Tuesday 28 December	Wednesday 29 December	Thursday 30 December	Friday 31 December	Saturday 01 January	Monday 03 January	Tuesday 04 January	Wednesday 05 January	Thursday 06 January	Friday 07 January	Saturday 08 January	Monday 10 January	Tuesday 11 January	Wednesday 12 January	Thursday 13 January	Friday 14 January	Saturday 15 January	Monday 17 January	Tuesday 18 January	Wednesday 19 January	Thursday 20 January	Friday 21 January	Saturday 22 January	Monday 24 January	Tuesday 25 January	Wednesday 26 January	Thursday 27 January	Friday 28 January	Saturday 29 January	Monday 31 January	Tuesday 01 February	Wednesday 02 February	Thursday 03 February	Friday 04 February	Saturday 05 February	Monday 07 February	Tuesday 08 February	Wednesday 09 February	Thursday 10 February	Friday 11 February	Saturday 12 February	Monday 14 February	Tuesday 15 February	Wednesday 16 February	Thursday 17 February	Friday 18 February	Saturday 19 February	Monday 21 February	Tuesday 22 February	Wednesday 23 February	Thursday 24 February	Friday 25 February	Saturday 26 February	Monday 28 February	Tuesday 01 March	Wednesday 02 March	Thursday 03 March	Friday 04 March	Saturday 05 March	Monday 07 March	Tuesday 08 March	Wednesday 09 March	Thursday 10 March	Friday 11 March	Saturday 12 March	Monday 14 March	Tuesday 15 March	Wednesday 16 March	Thursday 17 March	Friday 18 March	Saturday 19 March	Monday 21 March	Tuesday 22 March	Wednesday 23 March	Thursday 24 March	Friday 25 March	Saturday 26 March	Monday 28 March	Tuesday 29 March	Wednesday 30 March	Thursday 31 March	Friday 01 April	Saturday 02 April	Monday 04 April	Tuesday 05 April	Wednesday 06 April	Thursday 07 April	Friday 08 April	Saturday 09 April	Monday 11 April	Tuesday 12 April	Wednesday 13 April	Thursday 14 April	Friday 15 April	Saturday 16 April	Monday 18 April	Tuesday 19 April	Wednesday 20 April
3	TONY MCMASTER				4										Holiday	1/2 Holiday		Bank Holiday	Bank Holiday	Holiday	Holiday	Holiday		Bank Holiday									Holiday	Holiday		Sick	Sick	Sick	Sick	Sick							4												4							3											4												6												4							Holiday					4	Bank Holiday	Bank Holiday
4	WILLIE FINLAY										4					1/2 Bank Holiday		Bank Holiday	Bank Holiday					Bank Holiday					4						4						4					Holiday							4												4	3					4											Holiday							6												4												4	Bank Holiday	Bank Holiday
5	STAN WATTERS				4							Holiday				1/2 Bank Holiday		Bank Holiday	Bank Holiday					Bank Holiday					4												4					2	4												4						4												4						4												4												4							Bank Holiday	Bank Holiday
6	JIM MAXWELL										4					1/2 Bank Holiday		Bank Holiday	Bank Holiday					Bank Holiday											4	Holiday																	4					Holiday		Holiday											4												4												4												4							Bank Holiday	Bank Holiday
7	STEPHEN BLAIR															1/2 Holiday		Bank Holiday	Bank Holiday					Bank Holiday																																																																																									4	Bank Holiday	Bank Holiday
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2022 CALENDAR

Cell Formulas
Range	Formula
D2:DL2	D2	=IF(WEEKDAY(C2+1)=1,C2+2,C2+1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
DK3:DK7	Cell Value	="OTHER"	text	NO
DK3:DK7	Cell Value	contains "S"	text	NO
DK3:DK7	Cell Value	contains "H"	text	NO
DJ3:DJ7	Cell Value	="OTHER"	text	NO
DJ3:DJ7	Cell Value	contains "S"	text	NO
DJ3:DJ7	Cell Value	contains "H"	text	NO
E3:MA9	Expression	=OR(WEEKDAY(E$2)=7,WEEKDAY(E$2)=1)	text	NO
E7:N7,V8:V9,S7:AA7	Cell Value	="OTHER"	text	NO
E7:N7,V8:V9,S7:AA7	Cell Value	contains "S"	text	NO
E7:N7,V8:V9,S7:AA7	Cell Value	contains "H"	text	NO
E3:AA3,V4:V9,E4:N6,S4:AA6,G5:G9,O4:R9,S3:S7,X3:X7	Cell Value	="OTHER"	text	NO
E3:AA3,V4:V9,E4:N6,S4:AA6,G5:G9,O4:R9,S3:S7,X3:X7	Cell Value	contains "S"	text	NO
E3:AA3,V4:V9,E4:N6,S4:AA6,G5:G9,O4:R9,S3:S7,X3:X7	Cell Value	contains "H"	text	NO
E8:N9,S8:U9,AB3:GX7,W8:GX9,GY3:LW9	Cell Value	="OTHER"	text	NO
E8:N9,S8:U9,AB3:GX7,W8:GX9,GY3:LW9	Cell Value	contains "S"	text	NO
E8:N9,S8:U9,AB3:GX7,W8:GX9,GY3:LW9	Cell Value	contains "H"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A18	Any value
A20:A25	Any value
A9:A16	Any value
A3:A7	Any value
B1	Any value
C10:DL25	List	='Team Annual Leave'!$B$11:$B$28
E3:DL9	List	='Team Annual Leave'!$B$11:$B$28

2022 MALLUSK HOLIDAY PLANNER.xlsx
	A	B	C	D	E	F	G	H	I		K
58	BRANCH/DEPT	MALLUSK 2022
59	DATES OF PERIOD	24TH MARCH TO 20TH APRIL				MONTH		APRIL
60			Deductions (Day)		Overtime (Hrs)	Leave (Day)	Dates/Details of any deductions				Annual Leave dates (applicable if sick dates)
61	Employee Name	Start/Leaver Date	Sick	Other	OT	Annual Leave
62	TONY MCMASTER		0	0	8	1
63	WILLIE FINLAY		0	0	8	0
64	STAN WATTERS		0	0	8	0
65	JIM MAXWELL		0	0	8	0
66	STEPHEN BLAIR		0	0	4	0
67
68
69
MONTHLY PAYROLL SHEETS

Cell Formulas
Range	Formula
B58	B58	='2022 CALENDAR'!$B$1
C62	C62	=COUNTIF('2022 CALENDAR'!$CO$3:$DL$3,"SICK")+COUNTIF('2022 CALENDAR'!$CO$3:$DL$3,"1/2 SICK")/2
D62	D62	=COUNTIF('2022 CALENDAR'!$CO$3:$DL$3,"Other - Unpaid give details")
E62	E62	=SUM('2022 CALENDAR'!$CO$3:$DL$3)
F62	F62	=COUNTIF('2022 CALENDAR'!$CO$3:$DL$3,"Holiday")+COUNTIF('2022 CALENDAR'!$CO$3:$DL$3,"Unplanned Holiday")+COUNTIF('2022 CALENDAR'!$CO$3:$DL$3,"1/2 Holiday")/2
C63	C63	=COUNTIF('2022 CALENDAR'!$CO$4:$DL$4,"SICK")+COUNTIF('2022 CALENDAR'!$CO$4:$DL$4,"1/2 SICK")/2
D63	D63	=COUNTIF('2022 CALENDAR'!$CO$4:$DL$4,"Other - Unpaid give details")
E63	E63	=SUM('2022 CALENDAR'!$CO$4:$DL$4)
F63	F63	=COUNTIF('2022 CALENDAR'!$CO$4:$DL$4,"Holiday")+COUNTIF('2022 CALENDAR'!$CO$4:$DL$4,"Unplanned Holiday")+COUNTIF('2022 CALENDAR'!$CO$4:$DL$4,"1/2 Holiday")/2
C64	C64	=COUNTIF('2022 CALENDAR'!$CO$5:$DL$5,"SICK")+COUNTIF('2022 CALENDAR'!$CO$5:$DL$5,"1/2 SICK")/2
D64	D64	=COUNTIF('2022 CALENDAR'!$CO$5:$DL$5,"Other - Unpaid give details")
E64	E64	=SUM('2022 CALENDAR'!$CO$5:$DL$5)
F64	F64	=COUNTIF('2022 CALENDAR'!$CO$5:$DL$5,"Holiday")+COUNTIF('2022 CALENDAR'!$CO$5:$DL$5,"Unplanned Holiday")+COUNTIF('2022 CALENDAR'!$CO$5:$DL$5,"1/2 Holiday")/2
C65	C65	=COUNTIF('2022 CALENDAR'!$CO$6:$DL$6,"SICK")+COUNTIF('2022 CALENDAR'!$CO$6:$DL$6,"1/2 SICK")/2
D65	D65	=COUNTIF('2022 CALENDAR'!$CO$6:$DL$6,"Other - Unpaid give details")
E65	E65	=SUM('2022 CALENDAR'!$CO$6:$DL$6)
F65	F65	=COUNTIF('2022 CALENDAR'!$CO$6:$DL$6,"Holiday")+COUNTIF('2022 CALENDAR'!$CO$6:$DL$6,"Unplanned Holiday")+COUNTIF('2022 CALENDAR'!$CO$6:$DL$6,"1/2 Holiday")/2
C66	C66	=COUNTIF('2022 CALENDAR'!$CO$7:$DL$7,"SICK")+COUNTIF('2022 CALENDAR'!$CO$7:$DL$7,"1/2 SICK")/2
D66	D66	=COUNTIF('2022 CALENDAR'!$CO$7:$DL$7,"Other - Unpaid give details")
E66	E66	=SUM('2022 CALENDAR'!$CO$7:$DL$7)
F66	F66	=COUNTIF('2022 CALENDAR'!$CO$7:$DL$7,"Holiday")+COUNTIF('2022 CALENDAR'!$CO$7:$DL$7,"Unplanned Holiday")+COUNTIF('2022 CALENDAR'!$CO$7:$DL$7,"1/2 Holiday")/2
A62	A62	='2022 CALENDAR'!$A$3
A63	A63	='2022 CALENDAR'!$A$4
A64	A64	='2022 CALENDAR'!$A$5
A65	A65	='2022 CALENDAR'!$A$6
A66	A66	='2022 CALENDAR'!$A$7
A67	A67	='2022 CALENDAR'!$A$8
A68	A68	='2022 CALENDAR'!$A$9

 

[mrbenn]

Can anyone tell me if this is possible?