Formula to reference a different sheet based on date in field

[NWPhotoExplorer]

Hello all,

I have built a time entry sheet that keeps track of the time worked each day for the year. The sheets are called 2019, 2020, 2021. In cell B1, I have the year that the sheet is for. I'm am looking for a way I can pull the data for the final week of the previous year on a different sheet without having to hardcode in the sheet name.

I have tried google and a combination of SUBSTITUTE, CONCATENATE, etc, and cannot seem to come up with anything. I am hoping someone might know how to do this.

Work Hours.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		2022			WEEKLY HOURS										BEGINNING PTO BALANCE	PTO ACCRUED Total
2		YEAR STAR DATE
3		1/3/2022
4
5		Week	Start Date	End Date	Holiday Hours		PTO Hours	Regular Hours					Overtime Hours	Total Hours	PTO Total	PTO Accrural
6		52	12/27/2021	1/2/2022
7			Day	Date	Start Time (hh:mm)		Lunch Start (hh:mm)	Lunch End (hh:mm)		Break Start (hh:mm)	Break End (hh:mm)		End Time (hh:mm)	Hours (decimal)	Hour Type	PTO Accrual
8			Monday	12/27/2021	0:00		0:00	0:00		0:00	0:00		0:00
9			Tuesday	12/28/2021	0:00		0:00	0:00		0:00	0:00		0:00
10			Wednesday	12/29/2021	0:00		0:00	0:00		0:00	0:00		0:00
11			Thursday	12/30/2021	0:00		0:00	0:00		0:00	0:00		0:00
12			Friday	12/31/2021	0:00		0:00	0:00		0:00	0:00		0:00
13			Saturday	1/1/2022	0:00		0:00	0:00		0:00	0:00		0:00
14			Sunday	1/2/2022	0:00		0:00	0:00		0:00	0:00		0:00
15		Week	Start Date	End Date	Holiday Hours		PTO Hours	Regular Hours					Overtime Hours	Total Hours	PTO Total	PTO Accrural
16		1	1/3/2022	1/9/2022
17			Day	Date	Start Time (hh:mm)		Lunch Start (hh:mm)	Lunch End (hh:mm)		Break Start (hh:mm)	Break End (hh:mm)		End Time (hh:mm)	Hours (decimal)	Hour Type	PTO Accrual
18			Monday	1/3/2022	7:00		12:30	13:00					15:30
19			Tuesday	1/4/2022
20			Wednesday	1/5/2022
21			Thursday	1/6/2022
22			Friday	1/7/2022
23			Saturday	1/8/2022
24			Sunday	1/9/2022
Sheet1

Cell Formulas
Range	Formula
B6:D6,M8:M14,J8:K14,G8:H14,E8:E14	B6	='2021'!B526
D8	D8	=B3-7
D9:D14,D19:D24	D9	=D8+1
C16	C16	=D18
D16	D16	=D24
D18	D18	=B3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E16	Expression	=E16=0	text	NO
E6	Expression	=E6=0	text	NO
O16	Expression	=$O16>$AM16	text	NO
G16	Expression	=G16=0	text	NO
G6	Expression	=G6=0	text	NO
O6	Expression	=$O6>$AM6	text	NO

Thanks!

 

[Toadstool]

Hi NWPhotoExplorer,

You'll need to use INDIRECT.

Here I have "This is xxxx" in cell E3 of the shets 2020, 2021 and 2022 where xxxx is that year.
In my Sheet1 I have B1 with the year you want.
The INDIRECT in B6 builds the reference to retrieve the data: