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

Easier option... Highlight your 2nd column and do Find/Replace.

find Sector 1 replace with Sector 2

Do the same in columns 3 - 13, changing the 2 to 3 - 13.

HTH!

"Eric" wrote:

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

Thanks Sean. This is what I am doing now, though using thre Replace All with
external cell references is not as stratightforward as you might think, as I
have described in an earlier queastion.

"Sean Timmons" wrote:

Easier option... Highlight your 2nd column and do Find/Replace.

find Sector 1 replace with Sector 2

Do the same in columns 3 - 13, changing the 2 to 3 - 13.

HTH!

"Eric" wrote:

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

Hi,

Is this wat you mean

=INDIRECT("'[sector " & D4 & "]2008'!H6")

Where D4 contains a 1

Mike

"Eric" wrote:

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

Mike H,
This works. I have never used the INDIRECT function before and am not clear
on its purpose, but it seems to be perfectly designed for this problem.
Thanks,
Eric

"Mike H" wrote:

Hi,

Is this wat you mean

=INDIRECT("'[sector " & D4 & "]2008'!H6")

Where D4 contains a 1

Mike

"Eric" wrote:

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

Mike,
I'm afraid this solution has a couple of practical disqualifications, 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 in
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.
While the advantage of incorporating the Sector number at the top of each
column in the formula, is to make a single formula work for all columns, if
the row number doesn't doesn't change automatically, I would have to create a
different formula for every row.
Any other ideas?
Thanks,
Eric

"Mike H" wrote:

Hi,

Is this wat you mean

=INDIRECT("'[sector " & D4 & "]2008'!H6")

Where D4 contains a 1

Mike

"Eric" wrote:

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