Using text from a cell as part of a formula in another cell

[packerbacker1975]

This is probably pretty basic but... I have multiple ranges of cells that have formulas referencing values from other worksheets. I'm looking for a way to be able to change the worksheet name (A3) and have all of the cells in the range change to referencing that worksheet.

CEMS LTMPs for All Facilities.xlsm
	A	B	C	D	E	F	G	H	I
2				2022	2023	2024	2025	2026	2027
3	HEM	Hempstead	M	$61,100.00	$69,000.00	$46,100.00	$48,700.00	$159,000.00	$97,000.00
4		Analyzers		$60,000.00	$-	$30,000.00	$-	$75,000.00	$97,000.00
5		Sampling		$-	$-	$-	$12,000.00	$84,000.00	$-
6		DAHS		$-	$69,000.00	$-	$-	$-	$-
7		Computers		$1,100.00	$-	$1,100.00	$26,700.00	$-	$-
8		Miscellaneous		$-	$-	$15,000.00	$10,000.00	$-	$-
9		Major Projects		$-	$-	$-	$-	$-	$-
10		System Replacement		$-	$-	$-	$-	$-	$-
11		Cluster Servers		$-	$-	$-	$-	$-	$-
12		Data Loggers		$-	$-	$-	$-	$-	$-
13		Trace Upgrade		$-	$-	$-	$-	$-	$-
14		n/a		$-	$-	$-	$-	$-	$-
Overview

Cell Formulas
Range	Formula
D2:I2	D2	=HEM!M$4
D3:I3	D3	=SUM(D4:D8)
D4:I4	D4	=SUM(HEM!M$6:M$113)*1000
D5:I5	D5	=SUM(HEM!M$115:M$222)*1000
D6:I6	D6	=SUM(HEM!M$224:M$241)*1000
D7:I7	D7	=SUM(HEM!M$243:M$261)*1000
D8:I8	D8	=SUM(HEM!M$263:M$272)*1000
D9:I9	D9	=SUM(HEM!M$274:M$303)*1000
B3	B3	=LOOKUP(A3,$AB$2:$AB$48,$AC$2:$AC$48)
C3	C3	=LOOKUP(A3,$AB$2:$AB$48,$AD$2:$AD$48)
B10	B10	=$X$2
D10:I10	D10	=(SUMIF(HEM!$B$274:$B$303,"1",HEM!M$274:M$303))*1000
B11	B11	=$X$3
D11:I11	D11	=(SUMIF(HEM!$B$274:$B$303,"2",HEM!M$274:M$303))*1000
B12	B12	=$X$15
D12:I12	D12	=(SUMIF(HEM!$B$274:$B$303,"3",HEM!M$274:M$303))*1000
B13	B13	=$X$27
D13:I13	D13	=(SUMIF(HEM!$B$274:$B$303,"4",HEM!M$274:M$303))*1000
B14	B14	=$X$39
D14:I14	D14	=(SUMIF(HEM!$B$274:$B$303,"5",HEM!M$274:M$303))*1000

 

[Candyman8019]

Try something like: =indirect(A3&"!M$4",true) rather than =HEM!M$4 for example.

 

[packerbacker1975]

Try something like: =indirect(A3&"!M$4",true) rather than =HEM!M$4 for example.

Perfect!! Thanks so much!!