Bug 139910 - Calc saving document as .XLSX messes up formula (saving as .xls works fine)
Summary: Calc saving document as .XLSX messes up formula (saving as .xls works fine)
Status: NEEDINFO
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.0.3 release
Hardware: All All
: low minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
 
Reported: 2021-01-25 13:57 UTC by chaser.bruce
Modified: 2022-09-08 06:57 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Sample file (7.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-02-03 22:56 UTC, Ming Hua
Details

Note You need to log in before you can comment on or make changes to this bug.
Description chaser.bruce 2021-01-25 13:57:11 UTC
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:
/
Comment 1 Ming Hua 2021-01-26 07:48:22 UTC
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.
Comment 2 chaser.bruce 2021-01-26 15:51:50 UTC
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
Comment 3 Timur 2021-01-26 16:20:59 UTC
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).
Comment 4 Ming Hua 2021-02-03 22:47:44 UTC
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
Comment 5 Ming Hua 2021-02-03 22:56:56 UTC
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).
Comment 6 chaser.bruce 2021-03-11 11:15:54 UTC
Thanks Ming Hua for the POC.
Really aprichiate your help as I'm swamped.
Comment 7 Timur 2022-09-06 10:16:34 UTC
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.
Comment 8 Ming Hua 2022-09-08 02:14:16 UTC
(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.
Comment 9 Timur 2022-09-08 06:56:33 UTC
Yes. SUM($'Sheet 2'.A1;$'Sheet 3'.A1) works, SUM($'Sheet 2'.A1:$'Sheet 3'.A1) not.
Comment 10 Timur 2022-09-08 06:57:16 UTC
Different sheets are not range.