Vlookup another workbook and sheet using dynamic references

[AlanZA]

Hi,

I am trying to vlookup another workbook and sheet using cells as a reference. My equation is below and I can't seem to get it to work. It keeps giving a #REF ! error.

=VLOOKUP($A2,INDIRECT("'["&K1&"]"&J1&"'!"&"$A:$BE"),8,FALSE)

Please assist. Many thanks.

 

[Special-K99]

This is the part that's at fault

INDIRECT("'["&K1&"]"&J1&"'!"&"$A:$BE")

Place the formula in a blank cell, removing the INDIRECT and see what the result is, it should be a range

You can shorten it to this to make it more readable

INDIRECT("'["&K1&"]"&J1&"'!$A:$BE")

 

[steve the fish]

The formula looks fine to me. The reference error will come from whatever is in K1 and J1 not referencing a valid open workbook name and sheet name from that workbook.

 

[AlanZA]

When put in a blank cell I get the following:
'[Combined Tax Salary Sheet 2020.xlsm]SalMay19'!$A:$BE

Why won't it find the range on that sheet?

 

[steve the fish]

Does this error?

='[Combined Tax Salary Sheet 2020.xlsm]SalMay19'!A1

 

[AlanZA]

Yes unfortunately.

 

[steve the fish]

Then check your spelling or if it isnt already, open the workbook.

 

[AlanZA]

And by opening the reference workbook it now works. Does the reference workbook always need to be open for it to work?

 

[steve the fish]

Yes afraid so. Indirect doesnt work if its shut.

 

[AlanZA]

Oh well.

Thank you so much. This really has been helpful.