| Summary: | FILEOPEN: Opening .xlsx corrupts some INDIRECT formulas | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Cedrick Collomb <ccollomb> |
| Component: | Spreadsheet | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | CLOSED DUPLICATE | QA Contact: | |
| Severity: | normal | ||
| Priority: | medium | CC: | barta, cno, gerard.fargeot, gion_p_g, mariosv, sverson |
| Version: | 3.3.0 release | ||
| Hardware: | Other | ||
| OS: | Windows (All) | ||
| See Also: |
https://bugs.freedesktop.org/show_bug.cgi?id=70455 https://bugs.freedesktop.org/show_bug.cgi?id=79282 |
||
| Whiteboard: | BSA | ||
| i915 platform: | i915 features: | ||
| Attachments: |
Sample .xlsx document exhibiting the issue
New example showing SUM/AVERAGE/OFFSET/ADDRESS/INDIRECT |
||
Hi Cedrick, thanks for reporting. Seems a duplicate of: https://bugs.freedesktop.org/show_bug.cgi?id=70455 with the same issue but relative to 3.5.0 beta version. Similar but not strictly identical. Additional important information: works when loading .ods but does not work when loading .xlsx. Problem is not saving, it is in loading. If I create the formula in excel it works, if I load the document in LibreOffice it breaks. Also I am having the issue within AVERAGE and not inside SUM. confirmed in LibO 4.1.4.2 under Win7 64bit set status to NEW Forgot to mention that I unpacked the .xlsx after saving from LibreOffice and verified after saving that the content of sheet1.xml was accurate: <c r="D2" s="0" t="n"><f aca="true">AVERAGE(A1:INDIRECT("A"&D1))/12</f><v>0.541666666666667</v></c> This is still happening with 4.2.0.4 @Cedrick: was this OK in earlier versions, that you know ? Reproduce on 4.0.6, I do not have anymore earlier versions. This don't affect only INDIRECT, but also OFFSET and ADRESS (not sure about this one. This happenned when the : (separator for range_beginning and range_end) is followed by a function. Calc expect a reference here. Bug has been present at since 3.3.3 (also verified in 3.5.0 and 3.6.4). Hope this helps. Created attachment 93397 [details]
New example showing SUM/AVERAGE/OFFSET/ADDRESS/INDIRECT
Confirming this happens also with AVERAGE, SUM, OFFSET, ADDRESS, INDIRECT. Example .xlsx attached Confirming this happens also with INDEX. *** Bug 79282 has been marked as a duplicate of this bug. *** *** This bug has been marked as a duplicate of bug 70455 *** Just note that ADDRESS() returns a string and not a reference and can not be used with the range operator. In the second test case document the formulas in D6 and D7 are invalid anyway. |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.
Created attachment 91402 [details] Sample .xlsx document exhibiting the issue INDIRECT is removed from some cells Problem description: Steps to reproduce: 1. Create a spreadsheet and add a formula like this =AVERAGE(A1:INDIRECT("A"&D1))/12 2. Save spreadsheet as .xlsx 3. Close spreadsheet 4. Reload spreadsheet Current behavior: Cell formula after reloading is =AVERAGE(A1:A1("A"&D1))/12 Expected behavior: Loaded formula should be the same as when saved and created =AVERAGE(A1:INDIRECT("A"&D1))/12 Additional note: Saving the document as .ods does not exhibit this issue, but it is not interoperable with Excel so not a practical workaround. Operating System: Windows 7 Version: 4.1.3.2 release