John James wrote...
Hi Pete - Why are you saying you don't need the drive and path
information? When the files are saved, closed and later reopened, how
do you know where the source files are, especially if in different
drives/directories?
....
Excel won't allow you to open multiple workbooks with the same base
filename at the same time in the same Excel instance. If you had files
named c:\a\mine.xls and c:\b\mine.xls, you can't open them both at the
same time.
Why? Because Microsoft hasn't upgraded Excel's external reference
internals from the original version 1 functionality which only ran on
512K Macintoshes that had only one floppy disk drive that had a flat
file system. That meant there could only be one disk drive and no
subdirectories on that drive, so there could only ever be a single file
with any given filename that could be opened at any given time.
The presumably competent programmers on the Excel development team
haven't changed Excel to reflect that there are systems other than 22
year-old Mac 512Ks which may have multiple disk drives and hierarchical
file systems on any or all of those drives. That may be unfair to the
Excel developers; my own theory is that Microsoft's senior management
would much prefer separating their customers from their money while
having Microsoft employees do as little work as possible.
Anyway, Excel's external reference functionality maintains backwards
compatibility with hardware and OS that were obsolete once Mac SEs came
out in 1987. Since Excel hasn't been able to run on Mac 512Ks since,
what (i.e., I'm guessing), Excel 4 (early 1990s), for at least the last
13 years the external reference functionality of current versions of
Excel have been subject to the limitations of machines and OSs on which
those versions of Excel couldn't be run.
For comparison, Quattro Pro has allowed multiple files with the same
base filename to be open since version 1 back in 1990, and 123 has
allowed the same since Release 3.0 back in 1989. OpenOffice Calc and
Gnumeric also allow multiple files with the same base filename to be
open at the same time, but they're not MDI apps, so not entirely
comparable.
As far as this affects your problem, INDIRECT only works with *open*
files, only one file with a given base filename may be open at any
given time, so INDIRECT could only return values from that one open
file with the given base filename, so no purpose would be served
including the drive and directory in text references passed to
INDIRECT. If c:\a\mine.xls were open, INDIRECT could only return values
from it, not from c:\b\mine.xls even if you included the drive and
directory because when c:\a\mine.xls is open, c:\b\mine.xls can't be
open, and if it's not open, INDIRECT can't return anything from it.
Therefore, when using INDIRECT, there's no point including drive and
directory in the workbook reference. If the file you want is already
open, you can refer to it without using the drive and directory path.
If it's not open, you can't refer to it using INDIRECT.
