VLOOKUP another workbook where directory is based on cell values

[AntChr]

Hi there,

It's more a general question than purely related to the vlookup function. I am currently able to pull cell data into a worksheet from other workbooks. When I specify the directory in full, the data pulls through:

e.g. C:\User\Miscellaneous\Example - 1.xls

However, is it possible to use cell references in the formula to define the directory, such as:

e.g "C:\User\Miscellaneous"&D2&" - " &E2&".Xls"

It doesn't recognize the directory no matter what I try. I do have VBA for FileExists and CheckDir that allows me to use this form of formula. Would I need to do something similar in this instance to pull date in this way?

Any assistance would be much appreciated.

 

[kbrummert]

Use VLOOKUP(XX, INDIRECT(F2), 2, false) where F2 is a consolidated formula/cell where you have concatenated everything you have in your second e.g. The catch is that it won't read external workbooks unless they are open. It's something that MS has never fixed, even though this has frustrated millions of Excel users for decades.