| Summary: | Compatibility issues with cell summation formulas across worksheets | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | dongshili <dongshili> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | NEW --- | ||
| Severity: | normal | CC: | buzea.bogdan, erack, miguelangelrv, suokunlong |
| Priority: | medium | Keywords: | filter:xlsx |
| Version: | 7.2.0.4 release | ||
| Hardware: | All | ||
| OS: | All | ||
| See Also: | https://bugs.documentfoundation.org/show_bug.cgi?id=99986 | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
| Bug Depends on: | |||
| Bug Blocks: | 104839, 108917 | ||
| Attachments: | Sum formula error across the same cell of the worksheet | ||
Created attachment 174713 [details]
Sum formula error across the same cell of the worksheet
Confirmed Version: 7.2.0.4 (x64) / LibreOffice Community Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b CPU threads: 4; OS: Windows 10.0 Build 21390; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL Why is there no response to this requirement? (In reply to m.a.riosv from comment #2) Set to NEW per comment 2. This is still repro in a recent LO Dev 24.2.
Simplified case:
* In ODS and XLS:
=SUM($'1'.D5:$'3'.D5)
* But saved as xlsx:
=SUM('1':$'3'.D5:D5)
Notes for XLSX format (ODS and XLS are OK):
* Hard Recalculate results in #NAME? error for the formula.
* The initial worksheet in the formula lacks absolute worksheet reference ($).
* The range separator (:) is present twice in the formula; once for the worksheets and once for the cells (range).
I think there is some other bug report describing a similar behavior (but I don't recall whether it was also regarding 3D ranges / functions). Maybe Eike Rathke does recall such report(?). CC'ing.
FWIW, FTR and JIC (including Sheet Naming Restrictions):
https://help.libreoffice.org/latest/en-US/text/scalc/guide/rename_table.html
Fwiw, in test.xlsx for B3 the formula is saved as
<c r="B3" s="1" t="e">
<f aca="false">SUM('1':'10'!B3:B3)</f>
<v>#NAME?</v>
</c>
and apparently it was resaved after having been loaded and recalculated faulty already (#NAME? error as value); not containing the $ absolute reference flag is ok, as Excel does not know relative sheet references at all and sheet references are always absolute.
Saving =SUM($'1'.B3:$'3'.B3) to .xlsx results in SUM('1':'10'!B3:B3) as well so that part seems ok and import as =SUM('1':$'10'.B3:B3) is broken.
|
Description: =SUM($'1'.B3:$'10'.B3) Use this formula to calculate the sum of cells B3 in worksheets 1-10; After the first creation of the formula, it works fine, but after saving it as *.xlsx and re-opening the table, the formula becomes =SUM('1':$'10'.b3 :B3), and the cell displays an error message: #NAME? In my tests, the same tables and formulas were saved as *.xls and *.ods without error. So I think it's a formula compatibility issue. =SUM($'1'.B3:$'10'.B3) 使用这个公式来计算1-10工作表内B3单元格的和；在初次创建这个公式后，可以正常使用，但是保存为*.xlsx格式后，重新打开表格，公式就变成了=SUM('1':$'10'.B3:B3)，单元格内显示的错误信息是：#NAME? 经过我的测试，同样的表格和公式，当保存为*.xls和*.ods格式时，并没有发生错误。 所以我认为这是一个公式的兼容性问题。 Steps to Reproduce: 1.Open my attachment test.xls or test.ods, view the formula and result, and save it as *.xlsx Actual Results: Open a new XLSX file, look at the formula Expected Results: Find problems and solve them Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 7.2.0.4 (x64) / LibreOffice Community Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: zh-CN (zh_CN); UI: zh-CN Calc: CL