Thread: vlookup to a dynamic file name referenced in another cell

I would like to Vlookup to an excel file which is referenced in another cell so that I can change the file name in one cell and all the Vlookups that reference that cell will change accordingly.

So far I have only been able to achieve this by using the indirect function. However, this has the limitation that the other file must be open which makes the solution insufficient.

What I have using Indirect is:

=VLOOKUP($B13,INDIRECT($S$8&"!$A$14:$O$75"),4,FALSE)

.. where S8 contains the file name for which I would like to be able to modify the date from time to time:

[C:\filelocation\Template 9.30.11.xlsx]


If someone has a way to do this other than with the Indirect function so that the file does not need to be open to fully function it would be greatly appreciated.

Regards,
Chris

Thanks for your help NBVC. However, it seems this toolpack is available for excel 2010. Is there a way to get the INDIRECT.EXT function for Excel 2010?

Thanks again

Actually the add-on would probably be needed on everyone's PC who uses the spreadsheet, so that might not work. I guess I might have to learn the VB stuff.