reference a cell in path to file - indirect function?

[alfrodull]

What I'd like to do is reference a cell when pointing to the path of a
file. For example, I have a sheet like this:

TOTALA TOTALB
TOTALC
c:\path1!sheet1!A1 c:\path1!sheet1!B1
c:\path1!sheet1!C1
c:\path2!sheet1!A1 c:\path2!sheet1!B1
c:\path2!sheet1!C1
c:\path3!sheet1!A1 c:\path3!sheet1!B1
c:\path3!sheet1!C1
c:\path4!sheet1!A1 c:\path4!sheet1!B1
c:\path4!sheet1!C1
c:\path5!sheet1!A1 c:\path5!sheet1!B1
c:\path5!sheet1!C1
c:\path6!sheet1!A1 c:\path6!sheet1!B1
c:\path6!sheet1!C1


In these 3 columns. Ok, so it's a lot more than 3 columns, but you get
the idea. What I'd like to do is make the reference in cell A2 above
be c:\D2!sheet1!A1 and then have a fourth column with cell D2's value
set to path\to\filename.xls. Then when I do updates, I'd just update
that one cell and all the others in the row would automatically update.

Some places seemed to indicate that using the indirect() function would
make this work, but I just can't seem to get the syntax right.

Thanks

 

[Harlan Grove]

(e-mail address removed) wrote...

What I'd like to do is reference a cell when pointing to the path of a
file. For example, I have a sheet like this: ....
the idea. What I'd like to do is make the reference in cell A2 above
be c:\D2!sheet1!A1 and then have a fourth column with cell D2's value
set to path\to\filename.xls. Then when I do updates, I'd just update
that one cell and all the others in the row would automatically update.

Some places seemed to indicate that using the indirect() function would
make this work, but I just can't seem to get the syntax right.

INDIRECT can do this for open files, but not for closed files. The
technical reason is that INDIRECT must return range references, but
references into closed files result in arrays rather than ranges.

Your only options are given in the following archived article.

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

The INDIRECT.EXT function in Laurent Longre's MOREFUNC.XLL add-in is
now much more capable than it was, so it's now the simplest/quickest
approach to doing this, but there are a few machines and configurations
under which it just doesn't work but the alternatives do.