The CELL function with the "ADDRESS" option with a reference pointing to a cell in another worksheet, eg named MYSHEET is written as =CELL("ADDRESS"; MYSHEET.A1) returns the result in the form $MYSHEET.$A$1. However, when you change the name of this sheet, for example on YOURSHEET, the content of the formula will be changed and looks like this: =CELL("ADDRESS";$YOURSHEET.A1) but the result displayed in the cell does not change and still looks like the previous $MYSHEET.$A$1.
The result change will not take place after the sheet has been recalculated. This will happen only when the cell containing this formula is activated and the F9 key is pressed or after saving the file and reloading.
confirming, inherited from OOo
to make it work you can use e.g.:
to access the sheet name from current sheet use:
It works with hard recalc [Ctrl+Shift+F9]
So not clear to me it is a bug.
references updated or 'updateable', not 'broken' by the name-change (ctrl-shift-F9 works) but not displayed on screen (delayed recalc) imho is! a bug ...
*** This bug has been marked as a duplicate of bug 85986 ***