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.