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.
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...
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
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
(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.
I'll take this.
More appropriate summary.
*** Bug 74615 has been marked as a duplicate of this bug. ***
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.
4.2 backport request: https://gerrit.libreoffice.org/8165
*** Bug 75284 has been marked as a duplicate of this bug. ***
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.
Fixed.
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.
*** Bug 75671 has been marked as a duplicate of this bug. ***
Created attachment 95531 [details] Sample xlsx spreadsheet showing formula bug.
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.
XLSX is tracked in another bug and this one is explicitly for XLS only as it was clearly stated in the original summary!
Bug 75950 for those you care.
Please don't fiddle around with the Version field, it designates the earliest version the bug was discovered. Thank you.
*** Bug 76081 has been marked as a duplicate of this bug. ***
*** Bug 76211 has been marked as a duplicate of this bug. ***
*** Bug 76611 has been marked as a duplicate of this bug. ***
*** Bug 77758 has been marked as a duplicate of this bug. ***
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
*** Bug 77912 has been marked as a duplicate of this bug. ***