Expert: Gyula Gulyas - 8/11/2006

Question
Whenever I use another worksheet in a formula in excel it always uses the complete path to that worksheet.  My question is can you use a relative path to reference worksheets in excel? For example, ./worksheet.xls instead of c:/worksheet.xls.  When I've tried this it hasn't worked.  Thanks.

Answer
As far as I can tell this cannot be done. (well, at least easily).

as per Harlan Grove:

Along these same lines, is there any way to do a "relative" link or reference, similar to what you would do with a web page, whereby, you would basically tell Excel to "look for" the file to link to in either the same directory that the current (linked) file is in, or one or two levels up?
Does this make sense?  So that, using the same file names from below, you could move Y.xls and B.xls to the same directory, setup the links, then
move both of the files to D:\Z\ directory and when you open B.xls, still have it refer to Y.xls, because it is in the same directory still?

>Does this make sense?

...

Makes sense, is perfectly reasonable, and it just can't be done in Excel. A *LONG* time ago Microsoft wrote the original Excel version 1 for 512K Macs. Those beasts had a nonhierarchical file system and mostly only one floppy
drive and no harddrive, so there could only be one file open with a given base filename. And Microsoft decreed this was Good & Sufficient, and it hasn't changed at all since. Yes, all OS's under which the most recent 3 or 4 versions of Excel runs now have hierarchical file systems spanning
multiple local and networked drives, but Microsoft seems not to care to expend the resources to bring this bit of Excel functionality out of the mid
1980's.

Excel's external reference syntax/semantics preclude you from having more than one file open at a time with the same base filename. When the file is open, *only* the base filename in brackets appears in the external reference. When the file is closed, Excel prepends the drive and full
directory path to the bracketted base filename, where the drive and full directory path are where that file was more recently saved or accessed, whichever is more recent. It's that rather braindead semantics that makes
Excel so much more difficult to work with than 123 or Quattro Pro or StarOffice Calc or . . . These other spreadsheets let you enter relative paths like

+<<..\foo\bar.123>>A:A1..A:A1    (123)

and they will leave the path-and-filename as-is. Excel simply hasn't evolved from its original implementation in this respect. It's nice to imagine Microsoft fixing this, but it probably won't happen until well after they
provide built-in support for flashing text. Microsoft seems to prefer adding eyewash to adding serious features that have been in competing spreadsheets for over a decade.


See Harlan Grove's discussion at http://groups.google.ca/group/microsoft.public.excel.worksheet.functions/browse_...