Staying it its own directory.
 

I have a file with many vlookups from file1 to file2. The files are in the
same directory. Then I want to be able to email the files to another user
and they will put them in their directory. Their directory will not be the
same path as my directory. I was playing with an example and it caused me a
problem because when I set the files up in a new directory the vlookup() was
still pointing to the old directory. Is there a way in the vlookup() formula
to tell it to look in the current directory the file is located.

Thank you for your help.

Steven

Staying it its own directory.
 

"Steven" wrote...
I have a file with many vlookups from file1 to file2. The files are in the
same directory. Then I want to be able to email the files to another user
and they will put them in their directory. Their directory will not be the
same path as my directory. I was playing with an example and it caused me
a
problem because when I set the files up in a new directory the vlookup()
was
still pointing to the old directory. Is there a way in the vlookup()
formula
to tell it to look in the current directory the file is located.

There's no way to use relative pathnames in external references to closed
workbooks. The arguable good news is that if only one of the files contains
external references into the other, Excel will usually adjust the directory
path automatically if you open the file *without* external references
*FIRST* then open the file *with* the external references.

The alternative is using an Auto_Open macro in the workbook containing the
external references to change the directory path in formulas to the
workbook's own directory path.