Download it now!
Bug 74345 - Saving as xls incorrectly changes formula references.
Summary: Saving as xls incorrectly changes formula references.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: x86-64 (AMD64) Linux (All)
: medium major
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.2.2
Keywords: regression
: 74615 75284 75671 76081 76211 77758 77912 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-02-02 00:34 UTC by caxilbund
Modified: 2014-04-25 08:30 UTC (History)
12 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample spreadsheet with changing values when saved in xls xlsx (12.78 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-02 00:34 UTC, caxilbund
Details
Sample xlsx spreadsheet showing formula bug. (11.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-03-10 19:39 UTC, caxilbund
Details

Note You need to log in before you can comment on or make changes to this bug.
Description caxilbund 2014-02-02 00:34:14 UTC
Created attachment 93203 [details]
Sample spreadsheet with changing values when saved in xls xlsx

Formula results change and become incorrect after reopening spreadsheet when saved in xlsx or xls format.
Comment 1 Cor Nouws 2014-02-03 14:50:28 UTC
Hi caxibund,

thanks for your report!

For me with 4.2.0.4 on Ubuntu I see
- save as xlsx shows the same as in the ODS after refresh (Ctrl+F9)
- save as xls shows different result

How is that for you?

Futher, it looks as if in 4.1.5.1 
- save as xlsx shows different result (0 in D17)
- save as xls shows the same as in the ODS

hmm...
Comment 2 Cor Nouws 2014-02-03 14:54:16 UTC
Correction on previous comment: tried also in 4.1.4.2 (not in 4.1.5.1)

Opening files that I saved in 4.1.4.2 in 4.2.0.4 show the same vallue in 4.2.0 as in 4.1.4
Comment 3 subscriptions 2014-02-06 20:28:18 UTC
I see an issue importing a (largish) .xlsx file with 30 sheets, 800 rows, 40 or so columns.

In one cell C834 is a formula: "=C769"

A simple formula to duplicate a value in a list above.

The spreadsheet is listing economic data for countries. The cell C769 is meant to display the country (happens to be USA), but cell C834 shows a blank. (You can see the formula in the formula box, but not the value in the cell). The  cell above shows "==C772" -displays United Kingdom - the value of that cell - the cell below is also correct, a cell 4 cells below is also wrong. The same things happens on different sheets... but different cells/countries/ 
Seems quite random. 

I exported the sheet to  CSV, and the value is also absent. 

I opened the file in Microsoft Office and it is fine
I opened the file in Gnumeric and it is fine
I opened the file in LibreOffice 3.5.4.2 - also fine (though very slow to open).

Very strange. perhaps a Memory issue? - I have 4Gb of RAM.

I can send the file if required

LibreOffice_4.2.0.4_Linux_x86_deb
Comment 4 Kohei Yoshida 2014-02-21 15:53:10 UTC
(In reply to comment #3)
> I see an issue importing a (largish) .xlsx file with 30 sheets, 800 rows, 40
> or so columns.
> 
> In one cell C834 is a formula: "=C769"
> 
> A simple formula to duplicate a value in a list above.
> 
> The spreadsheet is listing economic data for countries. The cell C769 is
> meant to display the country (happens to be USA), but cell C834 shows a
> blank. (You can see the formula in the formula box, but not the value in the
> cell). The  cell above shows "==C772" -displays United Kingdom - the value
> of that cell - the cell below is also correct, a cell 4 cells below is also
> wrong. The same things happens on different sheets... but different
> cells/countries/ 
> Seems quite random. 
> 
> I exported the sheet to  CSV, and the value is also absent. 
> 
> I opened the file in Microsoft Office and it is fine
> I opened the file in Gnumeric and it is fine
> I opened the file in LibreOffice 3.5.4.2 - also fine (though very slow to
> open).
> 
> Very strange. perhaps a Memory issue? - I have 4Gb of RAM.
> 
> I can send the file if required
> 
> LibreOffice_4.2.0.4_Linux_x86_deb


This needs be filed as a separate bug. Thanks for your corporation.
Comment 5 Kohei Yoshida 2014-02-21 16:58:24 UTC
I'll take this.
Comment 6 Kohei Yoshida 2014-02-21 20:26:04 UTC
More appropriate summary.
Comment 7 Kohei Yoshida 2014-02-21 22:46:11 UTC
*** Bug 74615 has been marked as a duplicate of this bug. ***
Comment 8 Kohei Yoshida 2014-02-21 22:48:17 UTC
It was due to an incorrect export of shared formula ranges that involve references to another sheet. Excel doesn't use shared formulas for those references, and we were exporting them as shared formulas, which ended up corrupting the reference addresses.
Comment 9 Kohei Yoshida 2014-02-21 22:53:13 UTC
4.2 backport request: https://gerrit.libreoffice.org/8165
Comment 10 Kohei Yoshida 2014-02-21 22:55:49 UTC
*** Bug 75284 has been marked as a duplicate of this bug. ***
Comment 11 Commit Notification 2014-02-22 22:08:22 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=5ba682c48e449f30e3cc1ec4acac75a6122ee6d7&h=libreoffice-4-2

fdo#74345: Some shared formulas cannot be exported as shared formulas.


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 12 Kohei Yoshida 2014-02-23 01:10:13 UTC
Fixed.
Comment 13 Commit Notification 2014-02-26 07:01:03 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d51e120019e625e98bb793f77b6f1731fa125b1d&h=libreoffice-4-2-2

fdo#74345: Some shared formulas cannot be exported as shared formulas.


It will be available already in LibreOffice 4.2.2.

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 14 m.a.riosv 2014-03-02 16:28:00 UTC
*** Bug 75671 has been marked as a duplicate of this bug. ***
Comment 15 caxilbund 2014-03-10 19:39:10 UTC
Created attachment 95531 [details]
Sample xlsx spreadsheet showing formula bug.
Comment 16 caxilbund 2014-03-10 19:44:15 UTC
I have loaded 4.2.2.1. While the bug is fixed for xls sheets, it has not been fixed in xlsx spreadsheets. The behavior is exactly the same as before. The same is true of 4.1.5.3. I have added as an xlsx sheet as an attachment.
Comment 17 Kohei Yoshida 2014-03-10 20:25:12 UTC
XLSX is tracked in another bug and this one is explicitly for XLS only as it was clearly stated in the original summary!
Comment 18 Kohei Yoshida 2014-03-10 20:26:26 UTC
Bug 75950 for those you care.
Comment 19 Eike Rathke 2014-03-11 10:23:34 UTC
Please don't fiddle around with the Version field, it designates the earliest version the bug was discovered. Thank you.
Comment 20 m.a.riosv 2014-03-12 23:11:08 UTC
*** Bug 76081 has been marked as a duplicate of this bug. ***
Comment 21 m.a.riosv 2014-03-15 18:38:39 UTC
*** Bug 76211 has been marked as a duplicate of this bug. ***
Comment 22 m.a.riosv 2014-03-26 09:33:58 UTC
*** Bug 76611 has been marked as a duplicate of this bug. ***
Comment 23 m.a.riosv 2014-04-22 09:32:06 UTC
*** Bug 77758 has been marked as a duplicate of this bug. ***
Comment 24 Angel 2014-04-22 10:28:03 UTC
Estimado bugzilla-daemon@freedesktop.org,

Esta es una respuesta autom�tica.

Referente a su emil `[Bug 74345] Saving as xls incorrectly changes formula references.`.

Les informo que estar� fuera de la oficina de vacaciones hasta el 12/05/14.
Lamentablemente tengo poco acceso a mis emails, Si necesitan una respuesta urgente les
sugiero reenviar este email a alguna de las siguientes direcciones:

Por receptivo

MercedesM@rumbosur.com.ar
MariaV@rumbosur.com.ar
RaquelM@rumbosur.com.ar
GabrielaP@rumbosur.com.ar
GabrielaG@rumbosur.com.ar
CandelaM@rumbosur.com.ar

Por cruceros

AndreaV@rumbosur.com.ar

Muchas gracias
Angel Brisighelli


Dear bugzilla-daemon@freedesktop.org,

This is an automatic answer

Regarding your recent message  `[Bug 74345] Saving as xls incorrectly changes formula references.`.

I inform them that I will be out of the office on vacation until 05/12/14. 
Unfortunately I have limited access to my emails, If you need an urgent response 
suggest forward this email to any of the following addresses:

Incoming area

MercedesM@rumbosur.com.ar
MariaV@rumbosur.com.ar
RaquelM@rumbosur.com.ar
GabrielaP@rumbosur.com.ar
GabrielaG@rumbosur.com.ar
CandelaM@rumbosur.com.ar

Cruises area

AndreaV@rumbosur.com.ar


Thanks !
Angel Brisighelli

angelb@rumbosur.com.ar
Comment 25 m.a.riosv 2014-04-25 08:21:34 UTC
*** Bug 77912 has been marked as a duplicate of this bug. ***
Comment 26 Angel 2014-04-25 08:30:51 UTC
Estimado bugzilla-daemon@freedesktop.org,

Esta es una respuesta autom�tica.

Referente a su emil `[Bug 74345] Saving as xls incorrectly changes formula references.`.

Les informo que estar� fuera de la oficina de vacaciones hasta el 12/05/14.
Lamentablemente tengo poco acceso a mis emails, Si necesitan una respuesta urgente les
sugiero reenviar este email a alguna de las siguientes direcciones:

Por receptivo

MercedesM@rumbosur.com.ar
MariaV@rumbosur.com.ar
RaquelM@rumbosur.com.ar
GabrielaP@rumbosur.com.ar
GabrielaG@rumbosur.com.ar
CandelaM@rumbosur.com.ar

Por cruceros

AndreaV@rumbosur.com.ar

Muchas gracias
Angel Brisighelli


Dear bugzilla-daemon@freedesktop.org,

This is an automatic answer

Regarding your recent message  `[Bug 74345] Saving as xls incorrectly changes formula references.`.

I inform them that I will be out of the office on vacation until 05/12/14. 
Unfortunately I have limited access to my emails, If you need an urgent response 
suggest forward this email to any of the following addresses:

Incoming area

MercedesM@rumbosur.com.ar
MariaV@rumbosur.com.ar
RaquelM@rumbosur.com.ar
GabrielaP@rumbosur.com.ar
GabrielaG@rumbosur.com.ar
CandelaM@rumbosur.com.ar

Cruises area

AndreaV@rumbosur.com.ar


Thanks !
Angel Brisighelli

angelb@rumbosur.com.ar