Absolute cell references changes from reference sheet to current when I move one of sheet after reference. Example: I have spreadsheet having 3 sheet. sheet 1, sheet 2, sheet 3 Sheet 1 having a formula cell with absolute reference to sheet 2. say = B2.$sheet2.$B$2. If I move sheet 3 before sheet 1, formula changes to say = B2.$sheet1.$B$2.
Created attachment 92124 [details] Sample file Sample file attached. Step : 1 Add a sheet using plus button.[sheet4] 2 Then try to move sheet 4 before sheet 2. 3 then values in sheet 2 become zero & absolute reference changes to sheet 2 instead sheet 3.
Hi Jaise, thanks for reporting. Verified with: Win7x64Ultimate Version: 4.2.0.2 Build ID: cd65d6220c5694ee7012d7863bcde3455c9e3c30 Version: 4.3.0.0.alpha0+Build ID: 180271e987de9b80b4ecd826df960353b036b9fd TinderBox: Win-x86@39, Branch:master, Time: 2014-01-14_06:15:21 Regression from Version: 4.1.5.0.0+Build ID: 17f7655b6dedb7349c3ecd8445f119c4d14641b Following the steps in Comment#1, Sheet2.D2: =$Sheet3.$B$3*C2 changes to Sheet2.D2: =$Sheet4.$B$3*C2 References change without any apparent reason and without notice.
I'll take this.
It's not just moving sheets, but inserting or deleting sheets cause the same problem. I already have a fix for all 3 use cases.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=982a03d4e2baf4ca7e78c79014389d9b23e200d1 fdo#73655: Don't adjust formula tokens on non-top shared formula cells. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8c3b6b34cea6212f4f3f266cc92e76de97d0aa55 fdo#73655: Write unit test for this. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Backport request for 4.2: https://gerrit.libreoffice.org/#/c/7482/ and one for 4.2.0: https://gerrit.libreoffice.org/7483
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=946394352aafa791819768d0e7215b482f4c1082&h=libreoffice-4-2 fdo#73655: Don't adjust formula tokens on non-top shared formula cells. It will be available in LibreOffice 4.2.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Marking it closed.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=41b9f71f6717e9e4a6395227406c73b73c2d3a35&h=libreoffice-4-2-0 fdo#73655: Don't adjust formula tokens on non-top shared formula cells. It will be available already in LibreOffice 4.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Thanks Kohei. Verified fixed: Win7x64Ultimate Version: 4.2.0.4 Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71 Version: 4.2.1.0.0+ Build ID: 92346fb7714ca7c6a467771d8a8b01305c1b17d1 TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-01-31_00:15:33 Version: 4.3.0.0.alpha0+ Build ID: bab7eebba127d603a9f8011fed290627e2a64423 TinderBox: Win-x86@39, Branch:master, Time: 2014-01-31_00:56:43