vlookup with relative paths
 

is there a way to force excel to use relative paths instead of the full
path to the file in which the table array resides?

right now, vlookup looks for a particular file in a particular
directory, but if the file is moved, it breaks all the formulas. it
would be great if it would use the relative path (e.g. the folder in
which it is placed) to avoid such an error.

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=469937

dreamz wrote...
is there a way to force excel to use relative paths instead of the full
path to the file in which the table array resides?

right now, vlookup looks for a particular file in a particular
directory, but if the file is moved, it breaks all the formulas. it
would be great if it would use the relative path (e.g. the folder in
which it is placed) to avoid such an error.

Without VBA, no.

This often isn't a problem because if external references in one
workbook refer to files in the same directory. Move or copy the files
to another directory. When you open the file with the external
references, Excel will automatically adjust the path to the directory
containing the opened workbook.

However, if you have a workbook in X:\Y\foo referring to a table in
another workbook in X:\Y\bar and you want a relative path like
'..\bar', that requires VBA.