Renaming referenced worksheets

[Ian Mellors]

This is driving me mad,

is there a way to refence a worksheet other than by the
name that is displayed on the tab?

we have a workbook that has five weekly sheets. At the
end of each sheet there is a feild that is carried over
onto the next week's sheet. Easy, just make the formula
point to the field in sheet1 (=sheet1!E32).

However if the user renames the sheet (say Week 32)the
formula is broken.

Looking in the code view, Excel clearly knows internally
that sheet1 is still sheet1, with a name of 'week 32',
but can you get the formula to refer to the underlying
sheet number?????????

 

[Bob Phillips]

Something is driving you mad, but I doubt it is what you think.

If you have a reference to another sheet, and that sheet is renamed, the
reference is updated. So the formula

=Sheet1!E32

would change to

='Week 32'!E32

Excel does that.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ian Mellors]

Doh!

I'm sure it didn't do this before, Oh well, thanks Bob