vlookup to a dynamic file name referenced in another cell

**chrisac79** · 11-10-2011, 01:07 PM

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

**NBVC** · 11-10-2011, 01:15 PM

You can install a free addin from here: Morefunc and use INDIRECT.EXT instead of INDIRECT...

There are not many other ways except some complex VBA:

Check here for more info...

http://www.contextures.com/xlFunctions05.html

**chrisac79** · 11-10-2011, 01:49 PM

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

**chrisac79** · 11-10-2011, 02:46 PM

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.