Also it seems as though I've had issues with the beginning '. I *think* I
had to add an extra one to get it to work. Don't remember why. Did you
just try to put the value that's in C10 in the vlookup to see if that works?

"Mike McLellan" wrote:

Thanks Barb.

I tried this - the call now looks like
=VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
contents of cell C10 display as
'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

Any idea where I'm going wrong?

"Barb Reinhardt" wrote:

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index...func_Functions

"Mike McLellan" wrote:

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error