It seems that in Libre Office calc the format sheetname.cellreference is used to refer to a cell in another sheet whereas in excel it uses !. If I open an ods file from Libre Office in Excel it does not understand it which rathe defeats the object of an open format. What gives?
Well, ask Excel why they can’t read an official ISO format!
What you should do is, in Calc, save your file in Excel format and then it should open without any problem in Excel.
You must ask Microsoft. There are a lot of differences, see Differences between the OpenDocument Spreadsheet (.ods) format and the Excel for Windows (.xlsx) format - Microsoft Support
However Microsoft do claim support, https://techcommunity.microsoft.com/blog/microsoft365insiderblog/microsoft-365-apps-now-support-opendocument-format-1-4/4226735 so I guess they are working towards better compatibility
Neither of the links you shared refer to this issue so I am not optimistic. I actually tried to find the definition of ODS online but failed. I had hoped that I could share an ods file with someone NOT using LibreOffice and they would be able use it but this is not the case. I know I can export it as xlsx but that defeats the object of an open standard.
You didn’t explain, what specifically doesn’t work in Excel. And my guess is - you mean INDIRECT. Because otherwise, the references between sheets in ODS formulas like =Sheet2.A1 open fine in Excel.
And yes, it’s about Excel developers not paying attention to proper import and interoperability between different formats. They formally support the format, but don’t care if strings denoting addresses, coming from other formats, are interoperable. Calc, for example, when opening Excel file formats, switches to Excel notation; and even allows you to select Excel notation for formulas in ODS (which maybe you would have to do).
1 Like
Need clarification.
The attached file contains reference between sheets and opens normally in Excel 2016.
Example.ods (7.5 KB)
Apologies. I had completely misinterpreted the problem I was seeing. In fact the issue is with named data ranges. It seems the Excel can not import them from an ods file.
Example.ods (9.1 KB)
FTR: the part of the standard that discusses the address is https://docs.oasis-open.org/office/OpenDocument/v1.4/part3-schema/OpenDocument-v1.4-cs01-part3-schema.html#__RefHeading__1415614_253892949
And yes, I confirm that Excel 2016 fails to open it correctly. Please file a bug report to them.