Description: The cell formula does not reflect the updated result. Steps to Reproduce: 1.In the B1 of sheet "S1" add the fomula "=CELL("address",S2.$B$1)". 2.and then change the name of sheet "S2" as "S3" 3.but the formula result is not been updated. Actual Results: The correct formula result should be "$S3.$B$1" instead of the previous result "$S2.$B$1" Expected Results: The correct formula result should be "$S3.$B$1" Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.1.2 (X86_64) / LibreOffice Community Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129 CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win Locale: en-US (en-US); UI: en-US Calc: threaded
Just use the keyboard, either [F9] on the specific cell, or [CTRL]+[SHIFT]+[F9]. These are in menu Data > Calculate. Unless someone could demonstrate that this was not happening with the CELL() function before (I have not checked), then this would probably end up being NAB.
There is one detail that should be noted: The formula in the formula bar gets updated instantly without the need to force recalculate, so there is no problem there. The CELL() function is considered volatile, depending on its arguments.
For this to work, the formula cell containing CELL() would have to listen at a sheet rename event. Which currently isn't broadcasted as a data change at all, and also shouldn't in general except for CELL() and probably INDIRECT(). The reference appears updated because it refers only to the sheet number and when being viewed that is resolved to the current name.
(In reply to ady from comment #1) > Just use the keyboard, either [F9] on the specific cell, or > [CTRL]+[SHIFT]+[F9]. > > These are in menu Data > Calculate. > > Unless someone could demonstrate that this was not happening with the CELL() > function before (I have not checked), then this would probably end up being > NAB. Hi, ady, Thanks for your tips. What the cell formula does in my case, is to check the sheet name automatically, this is a trick I've used in MS office for a long time, and I would be very grateful for this approach in libreoffice.
(In reply to ady from comment #2) > There is one detail that should be noted: The formula in the formula bar > gets updated instantly without the need to force recalculate, so there is no > problem there. > > The CELL() function is considered volatile, depending on its arguments. I tried to reset below formula again ,and I also checked the Data> Calculate> AutoCalculate is on ,and I confirmed that formula result is not be recalculated again. formula := =CELL("address",S3.$B$2) result := "$S2.$B$2"
(In reply to Eike Rathke from comment #3) > For this to work, the formula cell containing CELL() would have to listen at > a sheet rename event. Which currently isn't broadcasted as a data change at > all, and also shouldn't in general except for CELL() and probably INDIRECT(). > > The reference appears updated because it refers only to the sheet number and > when being viewed that is resolved to the current name. Hi, Eike, the cell() function is what I needed. I use 'CELL("address",S1!$B$2)' in MS office, and it works fine, but it not work right in libreoffice.
(In reply to Eike Rathke from comment #3) > For this to work, the formula cell containing CELL() would have to listen at > a sheet rename event. Which currently isn't broadcasted as a data change at > all, and also shouldn't in general except for CELL() and probably INDIRECT(). * FWIW (OT), INFO() doesn't even react to recalculation (e.g. INFO("recalc") when going from Automatic to Manual is not updated by recalc hard). * FWIW, Google Sheets works in the same way as LO (i.e. CELL() doesn't listen at sheet rename event). (In reply to kenjfzhong from comment #5) > formula := =CELL("address",S3.$B$2) > result := "$S2.$B$2" Simple workaround: 1. In $Sheet2.$A$1, use the (volatile) function =NOW() 2. Wherever you want, use =CELL("address",$Sheet2.$A$1) 3. Rename the worksheet Sheet2 to whatever else you want. Result: since you have autocalculate ON, when you rename the worksheet the volatile function NOW() gets updated, and so the dependent CELL() function will get updated too.
(In reply to ady from comment #7) > (In reply to Eike Rathke from comment #3) > > For this to work, the formula cell containing CELL() would have to listen at > > a sheet rename event. Which currently isn't broadcasted as a data change at > > all, and also shouldn't in general except for CELL() and probably INDIRECT(). > > > * FWIW (OT), INFO() doesn't even react to recalculation (e.g. INFO("recalc") > when going from Automatic to Manual is not updated by recalc hard). > > * FWIW, Google Sheets works in the same way as LO (i.e. CELL() doesn't > listen at sheet rename event). > > > > (In reply to kenjfzhong from comment #5) > > formula := =CELL("address",S3.$B$2) > > result := "$S2.$B$2" > > > Simple workaround: > 1. In $Sheet2.$A$1, use the (volatile) function =NOW() > 2. Wherever you want, use =CELL("address",$Sheet2.$A$1) > 3. Rename the worksheet Sheet2 to whatever else you want. > > Result: since you have autocalculate ON, when you rename the worksheet the > volatile function NOW() gets updated, and so the dependent CELL() function > will get updated too. Ah! thanks for your workaround, it works! Thank you so much! Have a good day!
(In reply to Eike Rathke from comment #6) > (In reply to Eike Rathke from comment #3) > > For this to work, the formula cell containing CELL() would have to listen at > > a sheet rename event. Which currently isn't broadcasted as a data change at > > all, and also shouldn't in general except for CELL() and probably INDIRECT(). > > > > The reference appears updated because it refers only to the sheet number and > > when being viewed that is resolved to the current name. > > Hi, Eike, the cell() function is what I needed. > I use 'CELL("address",S1!$B$2)' in MS office, and it works fine, but it not > work right in libreoffice. Hi, Eike, thanks for your info, and it works. I just understood your message after the workaround explanation from ady. Thank you so much. Have a nice day!
Setting this bug to resolved worksforme is wrong, that NOW() thing is only a workaround, the underlying cause could be fixed.
(In reply to ady from comment #7) > Simple workaround: > 1. In $Sheet2.$A$1, use the (volatile) function =NOW() > 2. Wherever you want, use =CELL("address",$Sheet2.$A$1) > 3. Rename the worksheet Sheet2 to whatever else you want. Simpler workaround, force the formula cell itself to volatile: =CELL("address";Sheet2.A1)&T(NOW())
(In reply to Eike Rathke from comment #10) > Setting this bug to resolved worksforme is wrong, that NOW() thing is only a > workaround, the underlying cause could be fixed. Thinking out loud, as a simple user. Although I was not who set the WFM, I wonder whether changing the CELL() function to permanently be listening to rename events is really needed, when there is this simple workaround. As mentioned in comment 7, Google Sheets behaves as LO right now. I have not tested others. By changing this behavior, wouldn't this function then behave as "even more volatile", and thus resulting in even slower spreadsheets? Please allow me to use such inaccurate non-technical terminology; hopefully my intention is still understandable. At any rate, if a worksheet name is not used in the second argument, then listening to worksheet renaming should not be needed (and thus avoided), I guess/hope. IOW, if the change makes spreadsheets slower for everyone, perhaps it would be better that those users that would really need this behavior would use the workarounds. Just a thought.
(In reply to ady from comment #12) > I wonder whether changing the CELL() > function to permanently be listening to rename events is really needed, when > there is this simple workaround. > > By changing this behavior, wouldn't this function then behave as "even more > volatile", and thus resulting in even slower spreadsheets? How many times per minute do you rename a sheet?
I think this is a duplicate, with slightly different STR which are similar to bug 121422. *** This bug has been marked as a duplicate of bug 85986 ***