INDIRECT Function

[jeffmoseler]

I found a new handy function that allows me to write a formula that keeps the sheet name of the formula dynamic. It's pretty cool except that I barely understand how it works!

So I have a formula that looks like this: =VLOOKUP(B3,'[2019 BOB Goal Calculation.xlsx]2019'!$C$8:$D$20,2,0)

I would like to replace the Sheet reference (2019) with a dynamic year number so next year I don't have to change all the formulas. But I'm not sure how to integrate the two.

=VLOOKUP(B3,'[2019 BOB Goal Calculation.xlsx](INDIRECT($D$2&'"!$C$8:$D$20")),2,0)

I keep getting a syntax error. I'm hoping this is obvious to someone and they can help me figure out what the correct syntax should be.

 

[RoryA]

It would be:

=VLOOKUP(B3,INDIRECT("'[2019 BOB Goal Calculation.xlsx]"&$D$2&"'!$C$8:$D$20"),2,0)

 

[bobsan42]

Indirect provides a reference based on text string.
A reference consists of filename, sheet name and range address.
It either explicit or indirect.This goes to all parts of the reference.
You can't mix direct filename with indirect sheet and name. That's why you get an error.
However I believe you can't use INDIRECT on closed workbooks. So for such a formula to work the referred workbook must also be opened.