User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:41.0) Gecko/20100101 Firefox/41.0 Build Identifier: LibreOffice 5.0.3.2 These appears to be a regression - I was not getting this problem in 5.0.2 XLS uses <!> as the separator between workbook and sheets in the INDIRECT function. ODS uses <.> When switching between different formats, formulae become corrupted. Recommend that Calc is changed to handle both conventions. When use COL:COL (e.g $H:$H instead of $H$1:$H$999999) formula is corrupted Reproducible: Always Steps to Reproduce: 1. Create spreadsheet, use INDIRECT and COL:COL references in file 2. Save as XLS, save as ODS 3. ODS is corrupted [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes Reset User Profile?No
Created attachment 120191 [details] ods version
Created attachment 120192 [details] xls version
Hi @Elmar, thanks for reporting. Seems a duplicate. I think from 5.0.2, sure in 5.0.3 there is an option in: Menu/Tools/Options/LibreOffice calc/Formula - Detailed calculation settings - Custom - Reference syntax for string reference. If I'm wrong it can solve the issue. *** This bug has been marked as a duplicate of bug 92256 ***
Thank you, @m.a.riosv, this is very helpful. You are right, setting tools/options/calc/formula/formula syntax to "Excel A1" now means that <!> is also interpreted correctly in Calc. I confirm that that A:A vs. A1:A9999 issue also works correctly. As a matter of version control, I like to work on a base document in ods, odt, etc format, then save it to xls, doc, format with verion number and date for sharing with colleagues. Since they tend to be inconsistent with naming of files, it helps me to keep track of my own original work. I still ahve the problem that if I save the file to xlsx (MSOffice format from 2007), it loses the styles, when I reopen it in Calc. When opening in Excel, the cells display the right format, but the style definitions are missing.