Created attachment 193308 [details] Cell B4 should have been converted to =INDIRECT(B2)!INDIRECT(B3) The import filter for xls replaces the space (Excel intersect operator) in the formula correctly with an exclamation mark when opened in Calc. The same file, saved as .xlsx, gives an error ERR:509 missing operator when opened in LibreOffice. The space remains and has not been replaced. It fails in versions of LibreOffice I have tested from 6.4.7.2 to 28.8.0.0.alpha0 Issue arose in https://ask.libreoffice.org/t/intersection-value-using-named-ranges-and-indirect/103897 Attached .xlsx, which opens correctly in Excel 2010, has error in cell B4, easily corrected by manually entering an exclamation mark between the two INDIRECT references. Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: b38974391e8d4bf0d450abfaa86bbccbe1022995 CPU threads: 8; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: en-NZ (en_NZ); UI: en-GB Calc: CL threaded
Or rather, the Excel syntax (as configured in Options->Calc->Formula, and as set automatically on Excel file format import) must treat the space as intersection? See "Reference operators" at https://support.microsoft.com/en-gb/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a
The problem is that when importing and/or exporting the intersection operator (whether with Formula Syntax as Calc A1 or as Excel A1 or as R1C1) from/to xlsx, the import and/or export fails when using INDIRECT(). To be clear, the _argument_ for INDIRECT() is not the issue – the import/export in such case is already known to "fail", not portable, because it is always treated as a string. The case presented here is about intersecting between INDIRECT()'s. Using Calc A1 (!) syntax: =(INDIRECT("Column_1")!INDIRECT("Row_1")) ...where "Column_1" and "Row_1" are known range names, _seems_ to work. But save as a new xlsx file and reload > Err:508. OTOH: =(Column_1!Row_1) and: =SUM(Column_1!Row_1) will still work after saving as xlsx and reloading. This works correctly when saving as ods. Tested with recent 24.8 alpha.
A "programming language" that conditionally defines significant white space as operator is doomed to fail.. Anyway, probably the detection of "hey this whitespace is between two function calls that could return a cell range reference and thus it could be an operator" fails here.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/62e88a17fd5f803806a31ef6f56a241b55a88f5d Resolves: tdf#160371 Relax significant whitespace intersection pre-check It will be available in 24.8.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/+/168059 for 24-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-24-2": https://git.libreoffice.org/core/commit/4de8df5e5e848c38fc419dc1e55c656611cab1be Resolves: tdf#160371 Relax significant whitespace intersection pre-check It will be available in 24.2.5. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e10ca8d250b39cc2778e9df47a06fd6ba4f2fced tdf#160371: sc_subsequent_filters_test2: Add unittest It will be available in 24.8.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.
*** Bug 161787 has been marked as a duplicate of this bug. ***