putting a string from one cell in the formula of another -- indirect needed?

[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
=c:\path1!sheet1!A1 =c:\path1!sheet1!B1
=c:\path2!sheet2!A1 =c:\path2!sheet2!B1
=c:\path3!sheet3!A1 =c:\path3!sheet3!B1


What I'd like:

TOTALA TOTALB FILE
=[c1]!A1 =[c1]!B1 c:\path1!sheet1
=[c2]!A1 =[c2]!B1 c:\path3!sheet2
=[c3]!A1 =[c3]!B1 c:\path3!sheet3


In these 2 columns. Ok, so it's a lot more than 2 columns, but you get
the idea. What I'd like to do is make the reference in cell A1 above
be a concatenation of the path name in column c and the cell number in
the targetted file as in the pseudoformula above.

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. Maybe
because the other files aren't open???

Thanks

 

[Pete]

INDIRECT( ) can only get data from files which are open, although
others have referred to Harlan Grove's method of "pulling" data from a
closed file in recent postings - suggest you search for INDIRECT in the
archives. If your sheet names have spaces in them, you will have to
wrap apostrophes around the filename and sheetname as follows:

'c:\path1!sheet1'!A1

Hope this helps.

Pete

 

[alfrodull]

Sort of. Being able to pull data from the closed file is useful, but
I'm really more interested in how to correctly build the formula in one
cell from strings in other cells.

Ideally I'd have something like this:

=c:\data\ & A3 & .xls!A1

evaluate to:

=c:\data\path\to\myfile.xls!A1

I can also write a macro that reads through the list of file names and
assigns the right formula to the right cells, but that's hard to
maintain. I'd prefer to know if the above is possible and how to do it
if it is.

Thanks,

 

[Pete]

You would need to put the literal text within quotes, as follows:

="c:\data\" & A3 & ".xls!A1"

and if A3 = "path\to\myfile" then this would evaluate to

"c:\data\path\to\myfile.xls!A1".

If this formula was in A4, for example, then the formula =INDIRECT(A4)
(maybe entered in A5) would attempt to retrieve the value in cell A1 of
the file pointed to - it would succeed if the file was open, otherwise
it returns an error.

Hope this helps.

Pete