Indirect function reffering to closed excel files

[ceecld]

I generated the following formula hoping that it would automatically locate numbers on other spreadsheets.


=VLOOKUP($A4,INDIRECT("'"&C$27&"\["&B$2&".xls]"&B$2&"'!$F:$H"),2,FALSE)

Cell C27 contains file location (C:\data\harmonics\ etc.)
cell B2 contains file name and tab name


The formula takes a value on a summary sheet then using the column header finds the xls file which it is to look for a value.

The problem is that it only returns the value when the lookup spreadsheet is opened. I know its possible to do this with out the indirect function, however the reason im using the indirect function is so that i can copy this generric summary sheet into any folder and it will summarize my data.

Any ideas?

Thanks

 

[KenWright]

You cannot use the INDIRECT function with closed workbooks.

You might want to take a look at Laurent Longre's MOREFUNC.XLL add-in, available at

http://longre.free.fr/downloads/Morefunc.exe

The INDIRECT.EXT function in there works with most versions of Excel I believe.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------