Bug 75950 - EDITING: Saving as xlsx breaks formulas with sheet name in the references
Summary: EDITING: Saving as xlsx breaks formulas with sheet name in the references
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.6.3 release
Hardware: All All
: highest critical
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.3
Keywords: regression
: 76380 76784 (view as bug list)
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2014-03-09 14:20 UTC by m.a.riosv
Modified: 2015-12-05 15:13 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Base file to generate the xlsx with the bug. (16.61 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2014-03-09 14:20 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description m.a.riosv 2014-03-09 14:20:57 UTC
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/
Comment 1 Joel Madero 2014-03-09 17:33:45 UTC
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
Comment 2 m.a.riosv 2014-03-09 18:59:07 UTC
Hi Joel, thanks for adding to mab, seems a clear case for it.
Comment 3 Kohei Yoshida 2014-03-10 16:29:43 UTC
I'm on this.
Comment 4 Kohei Yoshida 2014-03-10 17:50:00 UTC
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.
Comment 5 Kohei Yoshida 2014-03-10 17:58:22 UTC
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...
Comment 6 Kohei Yoshida 2014-03-10 18:23:56 UTC
I'll exclude round-tripping with FORMULA functions which should be handled in a separate bug.  That appears to be an pre-existing problem.
Comment 7 Commit Notification 2014-03-10 18:27:02 UTC
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.
Comment 8 Commit Notification 2014-03-10 18:27:15 UTC
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.
Comment 9 Kohei Yoshida 2014-03-10 18:28:18 UTC
4.2 backport request: https://gerrit.libreoffice.org/8521
Comment 10 Commit Notification 2014-03-10 19:56:24 UTC
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.
Comment 11 Kohei Yoshida 2014-03-10 20:25:50 UTC
Fixed.
Comment 12 caxilbund 2014-03-17 16:59:43 UTC
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.
Comment 13 Kohei Yoshida 2014-03-17 18:11:17 UTC
(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.
Comment 14 m.a.riosv 2014-03-18 23:30:02 UTC
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
Comment 15 m.a.riosv 2014-03-31 00:00:44 UTC
*** Bug 76784 has been marked as a duplicate of this bug. ***
Comment 16 Kohei Yoshida 2014-04-25 16:05:22 UTC
*** Bug 76380 has been marked as a duplicate of this bug. ***
Comment 17 C A J 2014-04-25 18:05:59 UTC
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.
Comment 18 Nik Pap 2015-12-05 12:12:16 UTC
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 !!!
Comment 19 Nik Pap 2015-12-05 12:13:15 UTC
Sorry I meant to have the Comment 18 as bug reopened!
Comment 20 m.a.riosv 2015-12-05 15:13:11 UTC
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,