Bug 95513 - handling of INDIRECT function when switching between XLS and ODS formats
Summary: handling of INDIRECT function when switching between XLS and ODS formats
Status: RESOLVED DUPLICATE of bug 92256
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2015-11-02 05:11 UTC by Elmar
Modified: 2015-11-03 04:20 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

ods version (30.22 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-11-02 05:12 UTC, Elmar
xls version (41.50 KB, application/vnd.ms-excel)
2015-11-02 05:12 UTC, Elmar

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2015-11-02 05:11:25 UTC
User-Agent:       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:41.0) Gecko/20100101 Firefox/41.0
Build Identifier: LibreOffice

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
Comment 1 Elmar 2015-11-02 05:12:01 UTC
Created attachment 120191 [details]
ods version
Comment 2 Elmar 2015-11-02 05:12:30 UTC
Created attachment 120192 [details]
xls version
Comment 3 m.a.riosv 2015-11-02 12:19:38 UTC
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 ***
Comment 4 Elmar 2015-11-03 04:20:36 UTC
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.