Description: Sum formula changes across multiple tabs after saving to xlsx format. Steps to Reproduce: 1.Create a function that sums a cell across tabs 2.Save spreadsheet as xlsx type file (not an issue if saving as an ODS file) 3.Reopened in CALC - formula changed - Also opened in MS EXCEL and formula was incorrect. Actual Results: In my case, cell formula changed from: =SUM('Time (PN8181)'.F11:$'Time (Misc) - Last'.F11) to: =SUM('time (pn8181)':$'Time (Misc) - Last'.F11:F11) Sum shows as "0" - Does not show error even though formula is incorrect, unless cell edited. Expected Results: No formula change Reproducible: Always User Profile Reset: No Additional Info: I upgraded to this version. Bug was same on V24.4.x.x version as well. I have a copy of the spreadsheet if needed.
repro with: ____________________________________ Version: 24.8.2.1 (AARCH64) / LibreOffice Community Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13 CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3 Locale: de-DE (de_DE.UTF-8); UI: de-DE Flatpak Calc: threaded ______________________________________
Created attachment 197176 [details] test_stored_as_xlsx
The exported formula in OOXML is SUM('time (pnrst)':'time (misc) - last'!A1:A1) and as such should be correct, would be =SUM('time (pnrst)':'time (misc) - last'!A1:A1) in Excel A1 notation, but is parsed back badly. Same if Formula syntax is switched to Excel A1. (In reply to Eric Wilson from comment #0) > Also opened in MS EXCEL and formula was incorrect. What does Excel do with it? > Sum shows as "0" - Does not show error even though formula is incorrect, > unless cell edited. For me the correct sums are displayed when opening the attached document. That is because results are not recalculated when opening .xlsx unless forced under Tools -> Options -> Calc -> Formula, after recalculation the #NAME? error is displayed. It works if the sheets are renamed to time1 and time2 so the single quotes aren't needed and the expression is =SUM(time1:time2!A1:A1)
A quoted sheet name in a range of sheets in Excel notation apparently never worked.
(In reply to Eike Rathke from comment #3) > (In reply to Eric Wilson from comment #0) > > Also opened in MS EXCEL and formula was incorrect. > What does Excel do with it? It could be that Excel expects =SUM('time (pn8181):Time (Misc) - Last'!F11) Note the sick construct of concatenation of sheet names with : but the entire concatenation surrounded by single quotes, which only works because Excel does not allow : in sheet names. Otherwise it would be one quoted sheet name. Excel as ugly as only Excel can.
Created attachment 197184 [details] An Excel document with sheet names Sheel'1 and Sheet'2, and a cuboid formula
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/472f17d203207f1ef9d75ecfc9facda2d5f43384 Resolves: tdf#163554 Read/write Excel 3D reference as 'Sheet1:Sheet2'!C4 It will be available in 25.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/175495 for 24-8
Seems the Commit Notification bot is dysfunctional at the moment, that has been pushed to libreoffice-24-8, thanks Xisco. https://git.libreoffice.org/core/commit/b3adc88a5b73125bb6dd7f2ccb76ff6a382efd2e It will be available in 24.8.4.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/d8f71364a582e39a2fb04a09c9ae1bf8470c43c4 tdf#163554: sc_subsequent_export_test4: Add unittest It will be available in 25.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.