When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

Tony,
I'm afraid there may be no way. I've tried something similar to no avail.
It seems there should be a function similar to "indirect" which enables you
to dynamically refer to a cell based on a formula that builds the cell
location.

I'll be interested to see if anyone can provide you a solution. Sorry this
is not providing you what you seek (unless it makes you feel better that
someone has also failed to find an automated way to do this).

I'm guessing that maybe it can be done using Macros or Visual Basic
programming. But that is beyond me at this point.

"Tony M" wrote:

When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

You can use indirect if I understand you correct

See the help for the indirect function
with a sheet name in A1

=INDIRECT($A$1 & "!G3")




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tony M" <Tony wrote in message ...
When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

Tony

On Sheet2 I enter =Sheet1!A1

I change Sheet1 name to qwerty.

Sheet2 formula now reads =qwerty!A1

Excel 97, 2002 and 2003


Gord Dibben Excel MVP

On Mon, 21 Mar 2005 04:51:03 -0800, Tony M <Tony
wrote:

When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

Are you using indirect references to Sheet1?
If so
=MID(CELL("filename",Sheet1!A1),FIND("]",CELL("filename",Sheet1!A1,1)+1,255)&"!"
will display "Sheet1!"
and will change change when the name of Sheet1 changes

On Mon, 21 Mar 2005 09:37:33 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Tony

On Sheet2 I enter =Sheet1!A1

I change Sheet1 name to qwerty.

Sheet2 formula now reads =qwerty!A1

Excel 97, 2002 and 2003


Gord Dibben Excel MVP

On Mon, 21 Mar 2005 04:51:03 -0800, Tony M <Tony
wrote:

When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.