E
Eric
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?
Thanks,
Eric
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?
Thanks,
Eric