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.