Description: The Calc function INDIREKT() works incorrectly. If a document was imported from an xlsx file and saved as an ods file, these two problems then occur: 1. =INDIREKT("xyz.A1") produces #REF! with the separator "!" however not. => =INDIREKT("xyz!A1") 2. The table name cannot contain spaces, as this also results in #Ref! leads. => =INDIREKT("xyz xy!A1") Steps to Reproduce: 1.xlsx document save as ods document 2.use INDIREKT() with "." 3. Actual Results: 345 Expected Results: #REF! Reproducible: Always User Profile Reset: No Additional Info: Version: 7.3.7.2 (x64) / LibreOffice Community Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win Locale: de-DE (de_DE); UI: de-DE Calc: threaded
> 1. =INDIREKT("xyz.A1") produces #REF! with the separator "!" however not. => > =INDIREKT("xyz!A1") > ... > Steps to Reproduce: > 1.xlsx document save as ods document > 2.use INDIREKT() with "." Your description looks odd. Initially you write that the problem occurs when you use exclamation mark as separator; then in steps, you write about dot as separator.
PLease attach here your Excel (XLSX) document for testing
Created attachment 184492 [details] Excel file which was converted into an ods file
Created attachment 184493 [details] ODS file with bug
Aha. No, it doesn't "work incorrectly", and also, despite the original claim that it happens after "import from an xlsx", the described behavior occurs after creation from an *XLTX*. In version 5.1, a new string reference syntax was introduced: "Calc A1 | Excel A1", available under Options->Calc->Formula->Detailed Calculation Syntax->Details (tdf#92256). It allows using mixed Calc / Excel string references in a single spreadsheet; and it is used by default now, when one opens XLSX files. (It re-introduced what was used in old OOo versions without any specific syntax.) However, when creating files from an XLTX, the older (and stricter) "Excel A1" string reference mode is still used. It disallows the Calc-style string references. *Possibly* using "Calc A1 | Excel A1" here would also be reasonable, because Excel syntax could be used in pre-existing formulas coming from XLTX, and Calc syntax could be expected by users in the new documents ... but OTOH, this mixed mode is not ideal anyway. Documents containing such mixed syntax would fail when opened in Excel, so would not be interoperable. Why do you expect the mixed mode? IMO, you either should use ODS and its syntax (Calc A1), or you would need interoperability, and then use Excel A1 syntax - but not both. And thus, I'd say this is not a bug, and this behavior is better than more liberal mixed mode, requiring user to decide on the syntax explicitly if they intend to use Calc syntax when they use Excel templates (wouldn't users better use OTS templates in this case?). As for the space in the sheet name, the standards require that names containing special characters be enclosed into single quotes; so the proper reference would be =INDIRECT("'test Mittwoch'!A1")
(In reply to Mike Kaganski from comment #5) > Aha. No, it doesn't "work incorrectly", and also, despite the original claim > that it happens after "import from an xlsx", the described behavior occurs > after creation from an *XLTX*. Likely I was confused, using XLSX in my Downloads folder for testing. It seems like tdf#92256 had introduced also a compatibility setting written to XLSX documents; and thus, *some* XLSX documents may have it and define the mixed mode. In the *clear* XLSX documents, coming from MS Excel, Calc also sets the proper strict "Excel A1" syntax. So IMO: this is *not* a bug, but a proper behavior.