Linking Workbooks using file names in a range

[Mark T]

Looking to sum up several workbooks into one master consolidated book.
Want to have the list of names of files to sum up taken from a range,
like:

Col A Col B
FILE1 Actual file1 name here (could change)
FILE2 Actual file2 name here (could change)
FILE3 Actual file3 name here (could change)

The formula to sum the data is in each Cell on the master book (and is
the SAME Cell as on the individual sheets, just taking the data from
each one and summing it).

Would look something like this:

='C:\[FILE1.xls]FINANCE'!$E9+'C:\[FILE2.xls]FINANCE'!$E9'C:\[FILE3.xls]
FINANCE'!$E9

The File names could change but it shouldn't matter because the link is
looking for the range names FILE1, FILE2, FILE3.

It doesn't seem possible to get the links to work when the source is a
range name.

Any ideas?

 

[Guest]

check out indirect()
=indirect("'C:\["&B1&"]Finance'!$E9+...

 

[Harlan Grove]

check out indirect()
=indirect("'C:\["&B1&"]Finance'!$E9+...

....

You forgot the closing double quote and the right parenthesis. But
more fundamentally, INDIRECT doesn't work unless the other files are
open. Excel can't load multiple files with the same base filename, so
OPEN files NEVER need their drive/directory paths preceding their base
filenames.

If the OP needs the drive/directory paths, then the OP's files aren't
open when being accessed, and in that case INDIRECT won't work.

For alternative workarounds, see

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075