CODEWITHSUNDEEP
excelexcel-formula
Jarrad
Jarrad

Reputation: 11

Excel formula with a variable sheet name in range

I have a formula that has 8 references to a different sheet (it needed ISNA, ISBLANK, and IF statements so it snowballed).

Is there an easy way to change the value in this formulas cell reference without manually changing it every time?

For example:

INDEX('2404_Nov'!$J$2:$J$999999,MATCH(B2,'2404_Nov'!$B$2:$B$999999,0))

How would I make it as user friendly as I could to change the 2404_Nov reference for all occurrences?

Upvotes: 1

Views: 2135

Answers (1)

David
David

Reputation: 1232

Type the sheet name, 2404_Nov, into cell A1 on the sheet where you're entering your formula. Change your formula to:

=INDEX(INDIRECT(A1&"!$J$2:$J$999999"),MATCH(B2,INDIRECT(A1&"!B$2:$B$999999"),0))

The indirect function uses the value in cell A1 as a reference.

Upvotes: 1

Related Questions