E

#### Eric

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

:

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?