External sheetname variable in VLOOKUP

[artaries]

Hi

I've googled for hours for a solution on this, but couldn't find anything that seems sustainable.

In a given cell in Workbook 1 I have the following string:

=VLOOKUP($D$2,'[SBE.XLSM]KPI 2017'!$A$9:$AC$20,COLUMN(B$9:B$19),FALSE)

Both filename and sheetname should be a variable based on values of two different cells in Workbook 1 [A6 for Sheetname and A8 for Filename). The whole thing should work, even if the external file is closed.

Anyone?!

 

[Special-K99]

Try

=VLOOKUP($D$2,INDIRECT("'["&A8&".XLSM]"&A6&"'!$A$9:$AC$20"),COLUMN(B$9:B$19),FALSE)

I dont know if this will work on closed workbooks though.

 

[artaries]

Try

=VLOOKUP($D$2,INDIRECT("'["&A8&".XLSM]"&A6&"'!$A$9:$AC$20"),COLUMN(B$9:B$19),FALSE)

I dont know if this will work on closed workbooks though.

It doesn't. I already tried that and it works fine with an open workbook, but not otherwise.

An alternative I was thinking about, but don't know if it's possible, would be to use some kind of placeholder for filename and sheetname and upon entering a date in D2 it would trigger a find/replace across the range of cells that include the VLOOKUP. This find/replace could be a button as well.