You could use the INDIRECT function to "build" the reference *BUT* this
would require the source file to be open in order to work. This is usually
not desireable. An alternative is a free addin that might work for you (I've
don't have this particular addin but have seen it mentioned here quite
often).

Here's a link to the site but for some reason I'm currently unable to gain
acess to it (keep getting a 403 error)

Look for the Morefunc addin. You need the INDIRECT.EXT function.

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

Here's an alternative download site:

http://www.freedownloadscenter.com/B.../Morefunc.html

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command. I
can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used
the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works
fine.
In my case the pathname will remain constant, but the workbook and sheet
will change. I tried the following two commands and neither works VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating
the
full pathname for the file?