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.