Variable File and Path Names

Hi,

I'm trying to use the indirect function to reference other files that are defined as variables.

The formula I have is

=VLOOKUP("Sales",INDIRECT(file&[Book3.xls]Sheet1!$A$1:$G$7),3,FALSE)

Where 'File' is the variable name of the path of the file. This is currently set as:

C:\Documents and Settings\guykn\Desktop\

This returns #value

Can anyone help me and tell me what I'm doing wrong!

THANKS

=VLOOKUP("Sales",INDIRECT(a50&"[Book3.xls]Sheet1!$A$1:$G$7"),3,FALSE)
where a50 holds the path

Hi,

Thanks for this, it works even though there is no value in A50,

If I put in the cell ref d1 it falls over and i get the dreaded #ref

I've tried it both with the other file open and closed

Any Ideas?

Guy

If I put in the cell ref d1 it falls over and i get the dreaded #ref

I don't understand what you mean, did you enter the path in D1and replace A50 with D1 ? This is part of your lookup table ...

Hi,

yes, I replace the A50 with d1 which references the file path C:\Documents and Settings\guykn\Desktop\ and it does not work

A50 is not doing the lookup as 'Sales' is the first part of the lookup and the indirect part is looking is point to to the table in a particular file