Hi!
My vlookups currently pull from 15 different files and I would like for them to know automatically what file to pull from, instead of having to build 15+ different vlookups (one for each file).
The only variance between all my vlookup paths is the file name. See below a few examples:
=VLOOKUP($A54,'H : path\[LD.xlsx]MASTER'!$B:$U,5,FALSE)
=VLOOKUP($A64,'H : path\[UT.xlsx]MASTER'!$B:$U,5,FALSE)
=VLOOKUP($A74,'H : path\[FN.xlsx]MASTER'!$B:$U,5,FALSE)
These two letter abbreviations (LD, UT, FN) are already in cells B54, B65 and B74.
So originally, I was thinking to use something like this so that I could easily drag the formula down:
=VLOOKUP($A54,'"H : path\["&B54&".xlsx]MASTER"'!$B:$U,5,FALSE)
However, this does not work. I was able to do this with an INDIRECT formula, however this only works when all my work books are open, which is not an option for me (too many files). See formula below:
=VLOOKUP($A54,INDIRECT(B54),5,FALSE)
Does anybody have an idea of how to go around this? Thanks!!
Bookmarks