When using a formula to sum same cells (B12) on every sheet with formula =SUM($'01 2020'.B12:$'12 2020'.B12) and afterwords saving the document as .xlsx, on reopening the new .xlsx document the formula changes to =SUM('01 2020':$'12 2020'.B12:B12) and the field throws a #NAME? error and of course the calculation does not work.
Steps to Reproduce:
1.Create 3 sheets with spaces like so Sheet 1, Sheet 2, Sheet 3
2.On sheets 2 and 3 enter some value into fields A1
3.On Sheet 1 under A1 enter formula =SUM($'Sheet 2'.A1:$'Sheet 3'.A1) and calculate the SUM
4.Save file as .xlsx type and close the document.
5.Reopen the document and check the formula. It has now changed to =SUM('sheet 2':$'Sheet 3'.A1:A1)
6.The formula is now wrong and entering new values in A1 fields does nothing.
The formula is changed and wrong and entering new values in A1 fields does nothing.
This only happens if you save the document as .xlsx
User Profile Reset: Yes
I haven't tested your steps yet, but I'd like to point out that LibreOffice Calc and Microsoft Excel use different notations for sheets and cells in formula, and it's well documented in LO Help:
In short, Calc uses "$Sheet1.A1" by default ($ for sheet name, . between sheet name and cell name), while Excel uses "Sheet1!A1" by default (no symbol for sheet name, ! between sheet name and cell name).
So if you file is in Excel's XLSX or XLS format, and interoperability between Calc and Excel is important, you should consider using Excel's notations everywhere. In Calc this is configured in Tools > Options > LibreOffice Calc > Formula.
I am aware of different notations but we mostly use Libre Office for company employees.
I also tested the document in Excel and the xls one, shows the correct notations which are used by Excel but if I open the xlsx one the formula looks like this =SUM('01 2020':'12 2020'!B12:B12)
So it has added to extra ' signs and the formula is now wrong.
The correct one should be =SUM('01 2020:12 2020'!B12:B12)
I can send you a video or the files themselves if you would like
Bruce, please prepare and attach ODS so that's easy to test saving to XLSX.
Any report must be tested with daily master, good if you can do it yourself from https://dev-builds.libreoffice.org/daily/master/current.html (it's separate to working LO).
Reproduced with 7.1.0 using StR in comment #0:
Version: 18.104.22.168 (x64) / LibreOffice Community
Build ID: f6099ecf3d29644b5008cc8f48f42f4a40986e4c
CPU threads: 2; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: zh-CN (zh_CN); UI: zh-CN
Created attachment 169460 [details]
Here is a sample file prepared with the StR in comment #0.
Open the file, save as "Excel 2007-365 (.xlsx)" format. Close and open the .xlsx file again, the formula in $'Sheet 1'.A1 cell becomes "=SUM('sheet 2':$'Sheet 3'.A1:A1)", and gives #NAME? error (Re-calculate/F9 may be necessary).
Thanks Ming Hua for the POC.
Really aprichiate your help as I'm swamped.
This report is missing a key detail.
Are you using some language with separator of ":" or you are writing your formula wrongly, as separator should be ";" ?
Excel doesn't even read formula with : separator.
(In reply to Timur from comment #7)
> Excel doesn't even read formula with : separator.
Huh? Are you talking about the separator between function parameters, like =SUM(A1; A2) and can be either ; or , like =SUM(A1, A2).
We are talking about a different separator here, used in cell ranges like A1:D4, meaning the rectangular 4x4 cell area from row 1-4 to column A-D. I thought this is the same for MS Excel and Calc, and has this way for ages.
Yes. SUM($'Sheet 2'.A1;$'Sheet 3'.A1) works, SUM($'Sheet 2'.A1:$'Sheet 3'.A1) not.
Different sheets are not range.