Created attachment 95410 [details] Base file to generate the xlsx with the bug. From 4.2.1 saving in xlsx format, references with sheet name are broken, substituting the sheet name with #REF!! (f aca="false">SUM(#REF!!C3:C6)</f) Saving the attached file as xlsx, and reopen as xlsx, shows the issue. Regression from last working. Version: 4.2.0.4 Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71 Issue in: Win7x64Ultimate Version: 4.2.1.1 Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b Version: 4.2.3.0.0+ Build ID: f41da077c76ee8a70fbcf4fe62e0bfb1fabc1a1c TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-03-07_23:04:18 Version: 4.3.0.0.alpha0+ Build ID: 335a8a84fe6349fd716d4978346cfff9c884dd9b TinderBox: Win-x86@39, Branch:master, Time: 2014-03-07_23:59:04 From question in ask: http://ask.libreoffice.org/en/question/30929/loose-page-referance-in-calc-formulas-and-border-formatting-when-i-save-and-reopen-spread-sheets-libre-office-4211-english-calc-windows-81-english/
Thank you for reporting this issue! I have been able to confirm the issue on: Version: 4.3.0.0.alpha0 Date: Tue Feb 25 19:58:48 2014 +0100 Platform :Ubuntu 13.10 DE: GNOME3 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + As I've been able to confirm this problem I am marking as: New (confirmed) Critical - basic formulas being broken with xlsx save Highest Keywords - regression - m.a.riosv says it works in 4.2.0.4 so this is a minor release regression MAB4.2 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage and join us on freenode at #libreoffice-qa There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Hi Joel, thanks for adding to mab, seems a clear case for it.
I'm on this.
BTW, that attached file contains FORMULA function which Excel doesn't seem to have. When exporting it as xlsx those cells will break when opening in Excel. Just FYI.
Ah, Excel added FORMULATEXT function in Excel 2013 and we map our FORMULA to that one. Unfortunately I only have Excel 2007 here on my machine so I can't verify that part, but then this bug report is about references not the FORMULA function mapping...
I'll exclude round-tripping with FORMULA functions which should be handled in a separate bug. That appears to be an pre-existing problem.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5f5cce2ca3ed6aa05bdaa4cdb046efeb6904d9a3 fdo#75950: Add 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ca8ca8c28742879220f3ff88ae0d71c8d69b11cd fdo#75950: Just set the right grammar and be done with it. 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.
4.2 backport request: https://gerrit.libreoffice.org/8521
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=4f53e684ab1d44f67b544f5dc3c30e148a82007e&h=libreoffice-4-2 fdo#75950: Just set the right grammar and be done with it. It will be available in LibreOffice 4.2.3. 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.
Fixed.
I loaded 4.2.3.1. The bug does not yet appear to be resolved. While the reference in the cell appears to be correct, the value displayed is incorrect. For example, in the sheet I tested, the formula in the cell appears as =$Coefficients.I20 both before and after saving. However, after saving the sheet the value appears as 0 regardless of the value in the cell referenced although the correct value appears before saving the sheet.
(In reply to comment #12) > I loaded 4.2.3.1. The bug does not yet appear to be resolved. While the > reference in the cell appears to be correct, the value displayed is > incorrect. For example, in the sheet I tested, the formula in the cell > appears as =$Coefficients.I20 both before and after saving. However, after > saving the sheet the value appears as 0 regardless of the value in the cell > referenced although the correct value appears before saving the sheet. That's a different bug and is handled in different bug report.
Thanks Kohei. Verified Win7x64. Version: 4.2.3.1 Build ID: 3d4fc3d9dbf8f4c0aeb61498a81f91c5b7922f13 Version: 4.2.4.0.0+ Build ID: e1823627f35e4419880769fdd05acddbd0a9c25c TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-03-18_14:25:19 Version: 4.3.0.0.alpha0+ Build ID: 12ae7672f285da1d4c730315e8db23b3396b71cc TinderBox: Win-x86@39, Branch:master, Time: 2014-03-14_00:18:00
*** Bug 76784 has been marked as a duplicate of this bug. ***
*** Bug 76380 has been marked as a duplicate of this bug. ***
I can confirm that the specific (errant) behavior I originally reported is indeed fixed in Calc v.4.2.3.3. Thanks to all who orchestrated the correction. As a note to caxilbund's Comment #12, I reported a similar experience in Bug 76411, though it's not a bug. About the same time the sheet reference issue arose, I found that certain formulas were displaying numeric zero, even if they were simply copying a text entry from another cell. It turned out to be a manual versus automatic recalculation issue. At the guidance of m.a.riosv, I set the Recalculation-on-file-load option to Always and all is good. From the Calc menu, choose Tools > Options... > LibreOffice Calc > Formula.
I think a similar issue occurs as follows: Create 2 sheets, say Sheet1 and Sheet2 Type some numbers in cells of Sheet1. Then refer to them on Sheet2 e.g.: =Sheet1.A1 etc. Sum these the cells of Sheet2 e.g.: =SUM(A1:A3) Select the cell that has the sum (and the next to it) and move them lower. In Sheet2, the Sheet1 references will disappear, leaving you with =A1 !!!
Sorry I meant to have the Comment 18 as bug reopened!
I can't reproduce your issue with: Versión: 4.2.8.2 Id. de compilación: 48d50dbfc06349262c9d50868e5c1f630a573ebd Not being the same issue, please open a new bug report, is the way, and best to avoid long threads. You can comment about this one in the new. Reclosed as fixed,