J
Jon
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?
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?