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
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.