Vlookup

[aj]

Hi- I am using vlookup where the lookup table is in another file, I
have the file name in a cell of the sheet, please let me know how to
refer to the file name and range name in the vlookup function by using
the cell address that contains the file name.

 

[JE McGimpsey]

One way:

=VLOOKUP(A1,INDIRECT("'[" & A2 & "]Sheet1'!A:B"),2,FALSE)

However, for INDIRECT, the file must be open or you'll get a #Ref! error.

To work with closed workbooks, you could use the INDIRECT.EXT function
in Laurent Longre's MOREFUNC.xll add-in:

http://xcell05.free.fr/

 

[Dave Peterson]

One more option if the workbook is closed:

Harlan Grove wrote a User Defined Function that can pull from a closed
workbook:

http://groups.google.co.uk/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm