Variations on referencing a cell in another workbook - continued

[Eric]

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

:

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?

 

[Sheeloo]

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.

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

:

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?

 

[Eric]

Thanks Sheelo,
I am still not quite clear on the purpose of the INDIRECT function, but I
assume it allows the formation of the external cell reference with the
concatenation operator because it treats the characters in the first group in
parentheses as a text string until it executes the whole expression.
In any case, your suggestion worked, and allows me to use a single R1C1
formula for all cells in all 13 columns and 100 rows.
Eric

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.

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

:

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?

 

[Sheeloo]

You are right...only the reference need not be external (whatever you mean by
that)

This is from Excel Help for Indirect...

"Returns the reference specified by a text string. References are
immediately evaluated to display their contents. "

INDIRECT is needed when the reference is built dynamically... If you know
the complete reference then you can simply put a = before it and get the
value referred.. if you don't then you build it and then pass it to INDIRECT

Hope this clarifies...
(it is like a pointer in C language)

Thanks Sheelo,
I am still not quite clear on the purpose of the INDIRECT function, but I
assume it allows the formation of the external cell reference with the
concatenation operator because it treats the characters in the first group in
parentheses as a text string until it executes the whole expression.
In any case, your suggestion worked, and allows me to use a single R1C1
formula for all cells in all 13 columns and 100 rows.
Eric

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.

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

:

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?

 

[Eric]

OK thaks Sheelo, that's a good explanation. A function that allowed me to
build the cell reference (external to the workbook or not) dynamically is
exactly what I needed.
Eric

You are right...only the reference need not be external (whatever you mean by
that)

This is from Excel Help for Indirect...

"Returns the reference specified by a text string. References are
immediately evaluated to display their contents. "

INDIRECT is needed when the reference is built dynamically... If you know
the complete reference then you can simply put a = before it and get the
value referred.. if you don't then you build it and then pass it to INDIRECT

Hope this clarifies...
(it is like a pointer in C language)

Thanks Sheelo,
I am still not quite clear on the purpose of the INDIRECT function, but I
assume it allows the formation of the external cell reference with the
concatenation operator because it treats the characters in the first group in
parentheses as a text string until it executes the whole expression.
In any case, your suggestion worked, and allows me to use a single R1C1
formula for all cells in all 13 columns and 100 rows.
Eric

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.
:

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

:

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?