Bug 121422 - The CELL function does not change the result after changing the name of the sheet.
Summary: The CELL function does not change the result after changing the name of the s...
Status: CLOSED DUPLICATE of bug 85986
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calculate
  Show dependency tree / graph
 
Reported: 2018-11-14 16:21 UTC by Jerzy Moruś
Modified: 2020-04-14 19:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Jerzy Moruś 2018-11-14 16:21:27 UTC 
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.
Comment 1 Oliver Brinzing 2018-11-14 19:29:31 UTC 
confirming, inherited from OOo

to make it work you can use e.g.:
=CELL("ADDRESS";$YOURSHEET.A1)&LEFT(0*RAND();0)

to access the sheet name from current sheet use:
=MID(CELL("FILENAME");FIND("#$";CELL("FILENAME")&0*RAND())+2;256)
Comment 2 m_a_riosv 2018-11-14 22:02:50 UTC 
It works with hard recalc [Ctrl+Shift+F9]

So not clear to me it is a bug.
Comment 3 b. 2019-12-16 12:09:03 UTC 
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 ...
Comment 4 Eike Rathke 2020-04-14 19:17:00 UTC 

*** This bug has been marked as a duplicate of bug 85986 ***