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
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.