Suppose I want to refer to the value in Sheet2 Cell A1
then I will use this in Sheet1 A1

=INDIRECT("'Sheet2'!R" & ROW() & "C" & COLUMN(), FALSE)

this will evaluate to

=INDIRECT("'Sheet2'!R1C1", False)

False tells the Indirect function that reference style is R1C1.

You can copy it across and down and it will change the reference...

I think with this you can build your formula.
"Eric" wrote:

Mike,
I'm afraid this solution has a couple of practical problems, even
though it produces the right result. The main limitation is that when
copied, the entire string functions as an absolute. The specific cell
reference at the end of the formula !$H6 is written so that when copied, the
column (H) won't change but the row will. The spreadsheet has 100 rows for
each of the 13 columns. I would normally create the formula in the first
row, and then copy to all subsequent rows in that column, with the row number
increasing by 1 as the formula is pasted down the column. Using the
INDIRECT function that no longer works.
Do have any other ideas about this?
Thanks,
Eric

"Mike H" wrote:

Hi,

Is this wat you mean

=INDIRECT("'[sector " & D4 & "]2008'!H6")
Where D4 contains a 1

Mike

ORIGINAL QUESTION:
I have a simple problem. I have a master spreadsheet with about 1300 cells
referencing 13 other workbooks. The 13 workbooks all have the same name
except for the number they end with. All the cells referencing one of the 13
workbooks are in the same column and that column is headed with the unique
number for that workbook. It is an easy matter therefore to create the
unique name of each workbook by appending the number at the top of each
column using concatenation.
A simplified example of a reference to a given cell in a given external
workbook is ='[Sector 1.xls]2008 '!$H6 .
I have found that breaking up the reference with a concatenation operator
(&): ='[Sector &D4&.xls]2008 '!$H6, where cell D5 contains the number 1, does
not work as a cell reference, i.e., it doesn't bring back the value in the
remote cell.
I have tried to create the complete string, which exists in the formula
between single quotes, in a different cell, and then bring that string into
the cell attempting to execute the reference:
="'[Sector "&D4&".xls]2008" referenced from the original cell and then
concatenated to the last part of the formula: &!$H6
, but that hasn't worked, even though the string created in the external
cell is correct.
I have tried ending and re-starting the quotes within the file reference
before and after the concatenation, but that hasn't helped. In other words,
it seems that the syntax of the external cell reference string can't be
disrupted.
Does anyone have any insight into this problem, and can anyone offer a
solution?