Vlookup function with variable file names

[Gaurav]

Hi,

I need your help on this-
I want to vlookup data from different excel spreadsheets without
opening that particular file. Is that possible to link file path/name
and columns to be looked upon in the Vlookup formula as file names
would change every month, so that I don't have to enter the vlookup
formula again and again. And I only have to change the spreadsheet
name to change the vlookup formula. Would Indirect function be useful
in this case?

Thanks,

Gaurav

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.