Description: Example: 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. Actual Results: 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 Expected Results: / Reproducible: Always User Profile Reset: Yes Additional Info: /
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: https://help.libreoffice.org/7.0/en-US/text/shared/optionen/01060900.html 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.
Good day, 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: 7.1.0.3 (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 Calc: threaded
Created attachment 169460 [details] Sample file 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.
Dear chaser.bruce, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping
Dear chaser.bruce, Please read this message in its entirety before proceeding. Your bug report is being closed as INSUFFICIENTDATA due to inactivity and a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information (please ignore any that you've already provided): a) Provide details of your system including your operating system and the latest version of LibreOffice that you have confirmed the bug to be present b) Provide easy to reproduce steps – the simpler the better c) Provide any test case(s) which will help us confirm the problem d) Provide screenshots of the problem if you think it might help e) Read all comments and provide any requested information Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue. Please do not: a) respond via email b) update the version field in the bug or any of the other details on the top section of our bug tracker Warm Regards, QA Team MassPing-NeedInfo-FollowUp
Although I am not the original reporter, I believe this bug is still present in current versions of LO (7.4 – 7.6.alpha). STR: 1. Create 3 worksheets with space characters in their respective names. 2. In the last 2 worksheets, introduce some number in their respective cells A1. 3. Use a 3D formula in "$'Sheet 1'.A1" such as: =SUM($'Sheet 2'.A1:$'Sheet 3'.A1) (note the space characters in the names of the worksheets, and the use of "$" for absolute references for worksheets). 4. Save as ods. 5. Save as xls (97-2003) and close. 6. Open the ods file and save it as xlsx (2007-365). 7. Compare the results of the formulas: ods and xls > =SUM($'Sheet 2'.A1:$'Sheet 3'.A1) xlsx > =SUM('sheet 2':$'Sheet 3'.A1:A1) Note that 'sheet 2' lacks the "$". 8. On Recalculate Hard, the xlsx file will show the #NAME? error instead of the 3D formula. The other 2 files work as expected, and the values in the respective A1 cells can be updated and the formula will be updated; not so for the xlsx file. IOW, the export filter to xlsx is not producing the correct 3D formula. Considering that others have reproduced this behavior in the past too, I am setting this to NEW.