Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tony M
 
Posts: n/a
Default Absolute Worksheet reference number

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.


  #2   Report Post  
Mikedaspike
 
Posts: n/a
Default

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.


  #3   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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.




  #4   Report Post  
Gord Dibben
 
Posts: n/a
Default

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.


  #5   Report Post  
Bob Tarburton
 
Posts: n/a
Default

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM
Indirect reference from one worksheet to another Bill Sturdevant Excel Worksheet Functions 2 December 17th 04 02:23 PM
Reference Data in Moved Worksheet tommcbrny Setting up and Configuration of Excel 1 December 1st 04 07:49 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"