Using text in cells to build a link to within another file

[Guest]

BACKGROUND:

I have a file called summary.xls and 80 data files called data1.xls,
data2.xls, etc. Let's say that in the summary.xls file, cells A1-A80 contain
the text "data1.xls", "data2.xls", etc.

I'd like cells B1-B80 of summary.xls to equal cell C50 on Sheet1 of
data1.xls, cell C50 on Sheet1 of data2.xls, etc. One way to do this is to
type "=" into A2 of summary.xls and then open the file data1.xls and click in
cell C50 of Sheet1. Then repeat this process for all 80 files. Obviously,
that's a lot of work.


MY QUESTION:

I'd like to build a formula to put in cells B1-B80 that will pull the
filename from column A and create a formula like:

= '[data1.xls]Sheet1'!$C$50

To do this, I tried just putting the cell location in place of the
"data1.xls" in the example above so it looked like:

= '[A1]Sheet1'!$C$50

But the result was Excel trying to open a file called "A1". I also tried
converting the "A1" string above by putting:

= [T(A1)]Sheet1'!$C$50

But then Excel just tried to open a file called "T(A1)".

There must be a way to do this. Any ideas?

 

[Harlan Grove]

Jon wrote...
....

There must be a way to do this. Any ideas?

See

http://groups-beta.google.com/group...t.functions/msg/ac443753560f0075?dmode=source

(or http://makeashorterlink.com/?N12F25A8B ).

 

[RagDyer]

Check out this old post for an expansion of Harlan's first example, which I
use extensively in a large dB type WB.

http://tinyurl.com/8v8e5

The end of the thread is the pertinent post.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================