Look up across sheets... I think!!

[picklefactory]

Hi folks.
Hoping for some guidance please? I have a WB I use to chart works holidays, easy peasy, it consists of 13 sheets, 1 for each month and a final one I use to display current week plus next 2 weeks, always starting from the current weeks Monday. The 13th sheet is where my question lies. Currently, I manually copy/paste current weeks data plus subsequent 2 weeks from the individual sheets into the display sheet. I then have a PPT on an office screen, linked to this display sheet, displaying the current 3 week period so we can all see upcoming holidays for planning. The individual monthly sheets are in the same format as the display sheet, shown below. What might be my best route to have this sheet auto populate? Would it be a lookup from the 3 week display start date, or maybe an index/match scenario, or something else I'm not aware of? Would formula or VBA be the best route?
Thanks for looking

This is a section of display sheet

HOLIDAYS_2022.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	03/09/2021
2		Aug 30	Aug 31	Sep 01	Sep 02	Sep 03	Sep 04	Sep 05	Sep 06	Sep 07	Sep 08	Sep 09	Sep 10	Sep 11	Sep 12	Sep 13	Sep 14	Sep 15	Sep 16	Sep 17	Sep 18	Sep 19
3		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
27	Employee 24
28	Employee 25
29	Employee 26
30	Employee 27
31	Employee 28
Display

Cell Formulas
Range	Formula
B1	B1	=TODAY()
C2	C2	=B1-WEEKDAY(B1,2)+1
D2:W2	D2	=C2+1
C3:W3	C3	=C2
B27:B31	B27	=January!B27

And this is a section of a monthly sheet

HOLIDAYS_2022.xlsm
	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
2		Dec 01	Dec 02	Dec 03	Dec 04	Dec 05	Dec 06	Dec 07	Dec 08	Dec 09	Dec 10	Dec 11	Dec 12	Dec 13	Dec 14	Dec 15	Dec 16	Dec 17	Dec 18	Dec 19	Dec 20	Dec 21	Dec 22	Dec 23	Dec 24	Dec 25	Dec 26	Dec 27	Dec 28	Dec 29	Dec 30	Dec 31	Absences this month
3		Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat
27	Employee 24															P											B	B					3
28	Employee 25					V																					B	B					3
29	Employee 26							A																			B	B					3
30	Employee 27													S		H1											B	B					3.5
31	Employee 28																										B	B					2
December

Cell Formulas
Range	Formula
C2	C2	=November!AF2+1
D2:AG2	D2	=C2+1
C3:AG3	C3	=C2
B27:B31	B27	=January!B27
AH27:AH31	AH27	=COUNTA(C27:AG27)-((COUNTIF(C27:AG27,"H1")+COUNTIF(C27:AG27,"H2"))/2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:AG43	Cell Value	="P"	text	NO
C4:AG43	Cell Value	="H2"	text	NO
C4:AG43	Cell Value	="H1"	text	NO
C4:AG43	Cell Value	="V"	text	NO
C4:AG43	Cell Value	="S"	text	NO
C4:AG43	Cell Value	="A"	text	NO
C4:AG43	Cell Value	="B"	text	NO
C2:AG43	Expression	=WEEKDAY(C$2,2)>5	text	NO

 

[picklefactory]

OK, probably a bad move.... but I've been thinking.
Each sheet name is the relevant month, so I can create a cell on my display sheet that will contain the required month name by formatting as date "mmmmmmmmm", and then a 2nd cell from that via =TEXT that will display that month/sheet name as text. Then I can place a simple formula in each cell something like =INDIRECT($K$2&"!C3") where K2 is the cell with the required month as text, this will then select the correct sheet and cell range from that.
Problem though I can't drag that formula down as the C3 will stay as a constant. Is there a way around that to make the C3 element of that formula draggable?