Bug 92256 - INDIRECT function lost interoperabilty with calc documents migrated by OOo3.2.1
Summary: INDIRECT function lost interoperabilty with calc documents migrated by OOo3.2.1
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.2.2 release
Hardware: Other All
: medium normal
Assignee: Katarina Behrens (CIB)
QA Contact:
URL:
Whiteboard: target:5.1.0 target:5.0.2 target:5.0.4
Keywords:
: 80237 95513 (view as bug list)
Depends on:
Blocks: 93688
  Show dependency treegraph
 
Reported: 2015-06-22 14:36 UTC by Christoph Lutz
Modified: 2016-10-25 19:21 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
example document showing that indirect ranges with excel syntax are no longer supported (8.55 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-06-22 14:36 UTC, Christoph Lutz
Details
Screenshot/behaviour with OOo 3.2.1 (73.51 KB, image/png)
2015-06-22 14:36 UTC, Christoph Lutz
Details
Screenshot/behaviour with LO 4.4 (134.23 KB, image/png)
2015-06-22 14:37 UTC, Christoph Lutz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Christoph Lutz 2015-06-22 14:36:04 UTC
Created attachment 116733 [details]
example document showing that indirect ranges with excel syntax are no longer supported

The attached document contains 3 examples for the indirect function.

1) the first is a very simple reference: =indirect("A1")

2) the second is a reference to a cell in another sheet in excel-syntax: =indirect("Table2!A1")

3) the third is a reference to a cell in another sheet in calc-syntax: =indirect("Table2.A1")

All these examples were successfully evaluated by OOo 3.2.1. Look at the attached screenshot to see how OOo 3.2.1 behaves.

In LO up to 4.4 (I guess 5.0 and master, too), only two of these function are evaluated correctly. The result depends on the current setting of the option "tools->options->calc/formular/Formular-syntax". Look at the attached screenshot to see how LO 4.4 behaves.

If it is set to "Calc A1" (which is the default), then 1) and 3) are correct but 2) is not.

If it is set to "Excel A1", then 1) and 2) are correct, but 3) is not.

This simple behaviour change may lead our customer (city of munich) to serious problems with calc documents:

There is a big amount of excel documents that were migrated with the help of OOo 3.2.1 to ODS. They might use the indirect-function with excel strings (like in 2). On the other side, OOo 3.2.1 is already in use since a bunch of years, so there could be a lot of new documents created, that use the calc syntax (like in 3). We need to ensure that ALL documents will work with LibreOffice - old ones and new ones.

I know, that this seems to be very late for such a regression report, but the issue is brand new and did not show up before. On most PC's in munich there is still OOo3.2.1 used and the update to LibreOffice (here 4.1) is still in progress.
Comment 1 Christoph Lutz 2015-06-22 14:36:47 UTC
Created attachment 116734 [details]
Screenshot/behaviour with OOo 3.2.1
Comment 2 Christoph Lutz 2015-06-22 14:37:12 UTC
Created attachment 116735 [details]
Screenshot/behaviour with LO 4.4
Comment 3 Eike Rathke 2015-06-22 16:42:21 UTC
Well, the two sheet separators are different and referencing 3D blocks is different notation between Excel and OOo/LibO, additionally Excel knows the R1C1 reference syntax. Old OOo tried both sheet separators and 3D notations and whatever seemed resolvable was taken, this was successful in many cases but failed miserably in other cases. These two addressing schemes really should not be mixed in the same document.

We could only reimplement part of the "if the configured syntax fails try the other" with the error margin that maybe if detection then resolves a string that in fact should not had been resolved you're out of luck and get wrong values instead of error or other undefined behaviour. Doesn't sound very appealing to me..
Comment 4 Christoph Lutz 2015-06-22 16:49:39 UTC
Thanks, Eike, for your reply.

Do you have got an example (issue number) for such a case in which the combined logic did wrong things?
Comment 5 Eike Rathke 2015-06-22 19:09:18 UTC
The main reason is interoperability. OOo always tried the OOo notation first, so there were probably cases that worked in Excel but failed in OOo. If you need details you'll have to ask Kohei, he implemented the option 3 years ago.

For export, if mixed address conventions are exported to Excel, Excel will only calculate the Excel notation and the user will have a hard time to figure out what needs change. With the fixed notation setting the error will already appear in LibreOffice, before exporting to Excel.
Comment 6 m.a.riosv 2015-06-22 22:23:08 UTC
I'm not sure if the actual implementation is following ODF specification for INDIRECT().
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018426_715980110
Comment 7 Christoph Lutz 2015-06-23 09:32:58 UTC
(In reply to m.a.riosv from comment #6)
> I'm not sure if the actual implementation is following ODF specification for
> INDIRECT().
> http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.
> html#__RefHeading__1018426_715980110

Interesting question. Is this requirement met? "For interoperability, if the Ref text includes a sheet name, evaluators should be able to parse both, the “.” dot and the “!” exclamation mark, as the sheet name separator."

Both variants can be evaluated in the current version, but not at the same time. But the spec doesn't say "at the same time".

I think, "at the same time" is an implicit requirement. You cannot force the user to switch between different notation settings. We distinguish experienced users and "normal" users (I think you all know what I mean). Maybe an experienced user is able to do that, but not a "normal" user. And since these documents must work for experienced and "normal" users, it is clear to me, that manual switching is not an option.

What are the alternatives?

1) Change back logic to evaluates both variants at the same time? --> see Eikes concerns regarding potential feature loss in case of export to Excel.

2) Introduce another option "Calc A1 | Excel A1" which evaluates both variants at the same time. This option could be default in munich then (I don't say that this needs to be default for LO generally)

In both cases 1) and 2) we optionally try to recognize potential feature loss during Excel-export and warn the user. We could for example show a popup warning message and link to some help texts that describe how to avoid feature loss. 

3) Don't evaluate both variants at the same time, but switch the syntax-format automatically. Indicators could be:

 * If it is an Excel-Document (.xlsx, .xls), set "Excel A1"
 * In case of ODS-Documents, read setting from a new (LO specific) metadata-flag or fallback to default "Calc A1"

We could then add the ability to write this (LO specific) metadata-flag according to the current formular-syntax setting in tools->options (on save)

This way would it make easy to "repair" ods-documents with Excel syntax that were created by OOo. The Instructions for repairing could be: open the document, switch the syntax setting and save it back.

What do you think about these ideas?
Comment 8 Thorsten Behrens (CIB) 2015-06-23 16:32:58 UTC
(In reply to Christoph Lutz from comment #7)
> Interesting question. Is this requirement met? "For interoperability, if the
> Ref text includes a sheet name, evaluators should be able to parse both, the
> “.” dot and the “!” exclamation mark, as the sheet name separator."
> 
The standard says 'should', not 'shall'. Which means:

Should: Within normative text, should indicates "that among several
possibilities one is recommended as particularly suitable, without
mentioning or excluding others, or that a certain course of action is
preferred but not necessarily required."(ISO/IEC Directives, Part 2).

So no, this is recommended, but not mandated by the ODF standard.
Comment 9 Katarina Behrens (CIB) 2015-06-23 21:56:45 UTC
The status quo (INDIRECT function uses preferred formula syntax from the global settings and it is not possible to mix two different types of syntax in one document) is The Right Thing (TM) to do from the developers' and interoperability perspective. 
For the user, I can understand that in comparison with the past version ( < 4.0 ) this can be viewed as regression, as in their documents, there's suddenly #REF! error message where their formulas used to be. It's just that those older versions of LibO were bug-compatible

> In both cases 1) and 2) we optionally try to recognize potential feature
> loss during Excel-export and warn the user. We could for example show a
> popup warning message and link to some help texts that describe how to avoid
> feature loss. 

Please no popup warnings, it's really a bad UX style ...
 
> 3) Don't evaluate both variants at the same time, but switch the
> syntax-format automatically. Indicators could be:
> 
>  * If it is an Excel-Document (.xlsx, .xls), set "Excel A1"

Actually we already have this solution in place ( http://cgit.freedesktop.org/libreoffice/core/commit/?id=158b50763962f66515062300e265839828463efa ) for xls[x] documents produced by MS Office

It can't be done for ODF on filter level, sadly, as after the original Excel document has been saved (by whichever app) as ODF, no one can really tell it was an Excel document before. 

>  * In case of ODS-Documents, read setting from a new (LO specific)
> metadata-flag or fallback to default "Calc A1"
> 
> We could then add the ability to write this (LO specific) metadata-flag
> according to the current formular-syntax setting in tools->options (on save)

Yep, saving producent's string ref syntax (as a metadata flag, possibly in document settings) and using that syntax when loading the document would be a solution. It would however not repair existing documents automatically, some form of user intervention or another would be required in any case.
 
Correct me if I'm wrong, but the more I think about this issue, the more it seems to me that the best course of action here is to document the incompatibility (some errata? release notes?) and educate the users how can they repair their documents
Comment 10 m.a.riosv 2015-06-23 22:31:45 UTC
Thanks for a so clear explanation Thorsten, I think it's clear for me now.

Maybe the issue is in ODF definition, IMHO doesn't seem too much correct. on one side, such subtleties defining a standard, on the other, and even worse for me, allowing that two implementations conforming with the norm can lead to a different results.
Comment 11 Christoph Lutz 2015-06-24 09:39:40 UTC
The more I think about this issue, I think variant 3) is the correct choice. So this reply is just about points that touch variant 3): 

(In reply to Katarina Behrens (CIB) from comment #9)
> The status quo (INDIRECT function uses preferred formula syntax from the
> global settings and it is not possible to mix two different types of syntax
> in one document) is The Right Thing (TM) to do from the developers' and
> interoperability perspective. 

I now agree that a setting for different formula syntaxes is a good thing. It forces the user to make conscious decisions and in the end provides more control about what happens in case of exports. If I talk about "user" I mean this "experienced user" that is able to write the formula. This user needs to know what he is doing.

I don't see any reason for only supporting "preferred formula syntax from the
 global settings". I agree that it is useful to have a global default setting, but I think this setting should not be global, but document specific.

Just to make this clear: The attached example document is only a demonstration example. Yes, it contains both syntaxes in one document, but I don't think this is really an important requirement. So from my pov we don't need support for both syntaxes in the same document. But what we need is support for different syntaxes in different documents. I assume that our customer has documents with Excel- and documents with Calc-syntax and all these documents must work without forcing *ALL users* (even the "not experienced user", that just want to use the formula, that an "experienced user" has created) to know and understand the details about formula syntaxes. 

So I don't agree that the current behaviour is "yet the Right Thing", because it forces ALL users to switch between these settings, If a document is opened that uses a different syntax than the current selected one.

And this is the reason why I suggested in variant 3) to introduce a new document specific metadata and store the document specific setting there.

> For the user, I can understand that in comparison with the past version ( <
> 4.0 ) this can be viewed as regression, as in their documents, there's
> suddenly #REF! error message where their formulas used to be. It's just that
> those older versions of LibO were bug-compatible

I think we need to extend the existing behaviour up to a point that a "not experienced user" can work with these documents without noticing any change under the hood. And a broken document (what we currently have) will be noticed.

> > 3) Don't evaluate both variants at the same time, but switch the
> > syntax-format automatically. Indicators could be:
> > 
> >  * If it is an Excel-Document (.xlsx, .xls), set "Excel A1"
> 
> Actually we already have this solution in place (
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=158b50763962f66515062300e265839828463efa ) for xls[x] documents produced
> by MS Office
> 
> It can't be done for ODF on filter level, sadly, as after the original Excel
> document has been saved (by whichever app) as ODF, no one can really tell it
> was an Excel document before.

Yes, no algorithm is able to decide if a ODS document without the above mentioned metadata setting contains Excel or Calc syntax. But If we had the metadata, It would be easy to add this information. Just open the document, select the correct syntax and safe it back. These fixups are easy enough, so they can be done by supporters or the initial "experienced" authors very quickly. And the effect is that ALL other users can use the document without problems.

One might say: fixing up a document is already possible if you just change the "!" into a ".". Yes, this is true, but If I look at the concrete document (not the attached example document, but the document that we recieved by the customer), I see big and nested formulars there. Some contain two INDIRECT-functions in one formular. And all these formulars are repeated multiple times. Changing "!" to "." there has two disadvantages:

- The risk of introducing new bugs in formulars
- the loss of interoperability with Excel

So I think this kind of fixup is not what we want.

> 
> >  * In case of ODS-Documents, read setting from a new (LO specific)
> > metadata-flag or fallback to default "Calc A1"
> > 
> > We could then add the ability to write this (LO specific) metadata-flag
> > according to the current formular-syntax setting in tools->options (on save)
> 
> Yep, saving producent's string ref syntax (as a metadata flag, possibly in
> document settings) and using that syntax when loading the document would be
> a solution. It would however not repair existing documents automatically,
> some form of user intervention or another would be required in any case.
>  
> Correct me if I'm wrong, but the more I think about this issue, the more it
> seems to me that the best course of action here is to document the
> incompatibility (some errata? release notes?) and educate the users how can
> they repair their documents

I agree that manual fixups are probably the best thing we can do. Just to summarize the requirements:

- support for both syntaxes in different files is required

- fixups should be doable as easy as possible to avoid new risks and interoperability issues.

- unexperienced users shall not need to know details about formulars. Nobody wants to train / inform 15k-20k users because of this.

I think approach 3) could do that.
Comment 12 Christoph Lutz 2015-06-25 09:51:41 UTC
Just want to add some important details regarding approach 3:

* The options extras->options->calc/formula "formula syntax" and all other options on this page (except "Custom Settings"->"reference syntax for string references") have no influence on the INDIRECT function. So we do not do anything with these options.

* "Custom Settings"->"reference syntax for string references" directly influences the way how INDIRECT is evaluated - so this option needs to be persisted on document level (instead of ony being a global option). So the plan is to store this setting within ODS/OTS documents and restore it on load.
Comment 13 Eike Rathke 2015-06-29 12:49:58 UTC
I see only one proper way to solve this for existing documents that used both syntax notations at the same time and can't be adapted that easily, add options to that configuration, "Try Calc A1 first, then Excel A1/R1C1" and "Try Excel A1/R1C1 first, then Calc A1", maybe with an addition "(not recommended)", and evaluate accordingly in the INDIRECT function.
Comment 14 Katarina Behrens (CIB) 2015-06-30 11:07:35 UTC
Here's some executive summary of what needs to be done:

1a) (Re)Introduce legacy mode of INDIRECT func. evaluation, so that LibO behaves like OOo used to (what Eike says in comment 13 - try Calc syntax first, then Excel). Append this to 4 existing 'formula syntax for string reference' options 

1b) optionally implement also reverse mode (comment 13 - try Excel syntax first, then Calc) -- but this has lower prio as it is not needed for fixing this bug

2a) Save formula syntax for string reference as per-document setting in ODF (settings.xml)

2b) The same for OOXML, into extension namespace (extLst) (I'm afraid we can't do much for binary xls here)

3) If a formula syntax for string refence is not known or can't be determined during the import, use the legacy mode (point 1a)

4) ( ... yet to be clarified w/ clutz ... )
Comment 15 Christoph Lutz 2015-06-30 14:15:46 UTC
Thanks bubli, for this summary. 

I abbreviate "formula syntax for string references" as FSFSR

my +1 for 1a, 1b, 2a and 3

this also means that we have no longer a global FSFSR-setting, because this setting is always derived from a document by some logics (read explicit setting if present, heuristics for documents without explicit setting)

regarding 2b and 4:

I think an alternative solution for Export to excel could be to treat xls(x) documents always as FSFSR=EXCEL A1, in both directions, import and export.

This would also de facto prevent the users from storing documents with FSFSR=CALC A1 to xls(x):

0) let's asume I have got a spreadsheet with Indirect-Functions that uses strings in calc syntax. If this document is stored to xls(x), then the following things happen:

1) I get the typical "would you really like to export to an alien format" dialog

2) FSFSR is automatically switched to EXCEL. 

3) Now some formulars in the document are no longer evaluated as (possibly) expected. The user is forced to change the syntax of the strings in order to make the document running with EXCEL syntax evaluation.

4) if the file is stored for a second time, it is also interoperable with MS Excel.

With this idea, I would not store any settings to xls(x) documents, because it is automatically fixed to EXCEL.
Comment 16 Katarina Behrens (CIB) 2015-06-30 14:42:21 UTC
> With this idea, I would not store any settings to xls(x) documents, because
> it is automatically fixed to EXCEL.

You still didn't tell me *where* would you like to store those settings, if neither to a document, nor to user profile :)
Comment 17 Markus Mohrhard 2015-07-01 08:28:20 UTC
(In reply to Christoph Lutz from comment #15)
> Thanks bubli, for this summary. 
> 
> I abbreviate "formula syntax for string references" as FSFSR
> 
> my +1 for 1a, 1b, 2a and 3
> 
> this also means that we have no longer a global FSFSR-setting, because this
> setting is always derived from a document by some logics (read explicit
> setting if present, heuristics for documents without explicit setting)

We still need it for new documents. So the current global setting becomes the default value for new documents.

> 
> regarding 2b and 4:
> 
> I think an alternative solution for Export to excel could be to treat xls(x)
> documents always as FSFSR=EXCEL A1, in both directions, import and export.
> 
> This would also de facto prevent the users from storing documents with
> FSFSR=CALC A1 to xls(x):
> 
> 0) let's asume I have got a spreadsheet with Indirect-Functions that uses
> strings in calc syntax. If this document is stored to xls(x), then the
> following things happen:
> 
> 1) I get the typical "would you really like to export to an alien format"
> dialog
> 
> 2) FSFSR is automatically switched to EXCEL. 
> 
> 3) Now some formulars in the document are no longer evaluated as (possibly)
> expected. The user is forced to change the syntax of the strings in order to
> make the document running with EXCEL syntax evaluation.
> 
> 4) if the file is stored for a second time, it is also interoperable with MS
> Excel.
> 
> With this idea, I would not store any settings to xls(x) documents, because
> it is automatically fixed to EXCEL.

I'm sorry but this solution is unacceptable from several points.

The first one is obviously legacy documents, there are millions of documents out there created by Calc that use Calc A1 in OOXML documents.
Next it is not possible to automatically convert between Calc A1 and Excel A1 in the indirect function. As a result the only thing we could do is to issue a warning during saving that this will cause interoperability issues (well we are doing that already with the "alien format" warning. 

Changing the setting of the in-memory version is an absolute no-go from a UX and Calc perspective.
A user would not expect that saving changes the in-memory version of the document. Therefore it would silently introduce errors in the document which is unacceptable. Additionally it will cause issues if you save the document the next time in ODS as you are not saving the same document that you originally created.

All in all the only acceptable solution to mitigate the problem a bit is to move forward with a ref string syntax element for OOXML as well. The interesting thing is to write the algorithm for detecting the ref string syntax when there is no such element in an ods or xlsx document.
Comment 18 Christoph Lutz 2015-07-01 09:15:40 UTC
(In reply to Katarina Behrens (CIB) from comment #16)
> > With this idea, I would not store any settings to xls(x) documents, because
> > it is automatically fixed to EXCEL.
> 
> You still didn't tell me *where* would you like to store those settings, if
> neither to a document, nor to user profile :)

Yes, as moggi already noted did I suggest to have an in-memory version of this setting. I'm not the expert for the exact position, but I think it should be some property that belongs to a calc document and is preferrably accessable as a UNO-property e.g. with ThisComponent.StringReferencesFormulaSyntax.
Comment 19 Christoph Lutz 2015-07-01 12:10:58 UTC
(In reply to Markus Mohrhard from comment #17)
> (In reply to Christoph Lutz from comment #15)
> > Thanks bubli, for this summary. 
> > 
> > I abbreviate "formula syntax for string references" as FSFSR
> > 
> > my +1 for 1a, 1b, 2a and 3
> > 
> > this also means that we have no longer a global FSFSR-setting, because this
> > setting is always derived from a document by some logics (read explicit
> > setting if present, heuristics for documents without explicit setting)
> 
> We still need it for new documents. So the current global setting becomes
> the default value for new documents.

ok, agree. Question:

how to represent and distinguish document specific FSFSR and default FSFSR settings in the GUI? I first thought, we could reuse the existing "global FSFSR" dialog and just make it document specific. Should we add another option "formula syntax for string references in new documents"? (have no better idea ATM)

> > regarding 2b and 4:
> > 
> > I think an alternative solution for Export to excel could be to treat xls(x)
> > documents always as FSFSR=EXCEL A1, in both directions, import and export.
> > 
> > This would also de facto prevent the users from storing documents with
> > FSFSR=CALC A1 to xls(x):
> > 
> > 0) let's asume I have got a spreadsheet with Indirect-Functions that uses
> > strings in calc syntax. If this document is stored to xls(x), then the
> > following things happen:
> > 
> > 1) I get the typical "would you really like to export to an alien format"
> > dialog
> > 
> > 2) FSFSR is automatically switched to EXCEL. 
> > 
> > 3) Now some formulars in the document are no longer evaluated as (possibly)
> > expected. The user is forced to change the syntax of the strings in order to
> > make the document running with EXCEL syntax evaluation.
> > 
> > 4) if the file is stored for a second time, it is also interoperable with MS
> > Excel.
> > 
> > With this idea, I would not store any settings to xls(x) documents, because
> > it is automatically fixed to EXCEL.
> 
> I'm sorry but this solution is unacceptable from several points.
> 
> The first one is obviously legacy documents, there are millions of documents
> out there created by Calc that use Calc A1 in OOXML documents.

wow, this confuses me. Isn't this exaclty the situation that you wanted to prevent by introducing this global FSFSR setting? OOo had no problems with 

So it is clear, that these Excel documents will never be shown correctly by the typical default application MS Excel.

Clear, this could happen in legacy documents, but I think this was a mistake of the past and we should do better in future. I still think it would be better to prevent users from creating MS Excel incompatible XLS(X) documents.

> Next it is not possible to automatically convert between Calc A1 and Excel
> A1 in the indirect function. As a result the only thing we could do is to
> issue a warning during saving that this will cause interoperability issues
> (well we are doing that already with the "alien format" warning. 

I don't like the current "alien format" warning dialog. It misses transparency. As a user I would like to know, which exact features of my current document will be lost during export. I think I once saw that MS Office showed me a dialog with a list of features that will get broken during export into an "old format" like rtf. If we had such list in LibreOffice, too, It would be easy to add a new list entry like "your formula syntax for string references is changed to EXCEL A1, please check your strings". This is what I would expect if I export into an alien format.

But ok, such a feature would cost a lot of time (probably too much time) and is of course a completely different tasks, so we don't need to deepen that here.

But this should be no excuse for not improving the above situation.

> 
> Changing the setting of the in-memory version is an absolute no-go from a UX
> and Calc perspective.

I don't stick at the suggestion to automatically change the FSFSR during export. Maybe we could find another way of forcing the user to change FSFSR manually? But we first need to get to a common sense whether we want to avoid the future creation of non interoperable XLS(X) documents (with FSFSR =! EXCEL A1)? 

> A user would not expect that saving changes the in-memory version of the
> document. Therefore it would silently introduce errors in the document which
> is unacceptable.

It is not "silently" due to the "alien format" warning.

With the current LO version, it is possible to silenty produce non interoperabe XLS(X) documents (if FSFSR != EXCEL A1). The question here is: when does the user recognize that something is wrong?

I suggest to force the user (in either way) to recognize the really existing interop issue as soon as possible - which means best in the current calc session. At the moment, you need first to open the document in Excel (which could be months later) before you recognize the interop issue (maybe you hope that the issue is never discovered?).
 
> Additionally it will cause issues if you save the document
> the next time in ODS as you are not saving the same document that you
> originally created.

agree for the workflow: open an existing ODS document with FSFSR=CALC, export the document to XLS(X); do nothing else with the XLS(X); open the XLS(X) document in LO and store it back to ODS.

How important is this workflow? Why are you exporting it to XLS(X) if you want to do nothing with it inbetween?

> All in all the only acceptable solution to mitigate the problem a bit is to
> move forward with a ref string syntax element for OOXML as well.

Just thinking about bubli's "I'm afraid we can't do much for binary xls here". I would prefer a solution that is consistent for XLS and XLSX. The above suggestion is.

> The
> interesting thing is to write the algorithm for detecting the ref string
> syntax when there is no such element in an ods or xlsx document.

OK. Since we should get a common sense about the heuristic for the case that ODS and XLSX documents don't have got a FSFSR setting, I will write down some suggestions:

docFSFSR is the FSFSR-setting that comes with the document

Import-heuristic 1)
1.a) if XLS(X) document, set FSFSR=EXCEL A1
1.b) if ODS && !docFSFSR, set FSFSR="CALC A1 or EXCEL A1"
1.c) if ODS && docFSFSR, set FSFSR=docFSFSR
1.d) if new document, set FSFSR=default-Value from profile

Import-heuristic 2) [try to be more case/creator specific]
2.a) if XLS(X) && !docFSFSR && creator=MS, set FSFSR=EXCEL A1
2.b) if XLS(X) && !docFSFSR && creator=LO, set FSFSR="CALC A1 or EXCEL A1"
2.c) if XLS(X) && docFSFSR, set FSFSR=docFSFSR
2.d) if ODS && !docFSFSR && creator=LO, set FSFSR=CALC A1
2.e) if ODS && !docFSFSR && creator=OOo, set FSFSR="CALC A1 or EXCEL A1"
2.f) if ODS && docFSFSR, set FSFSR=docFSFSR
2.g) if new document, set FSFSR=default-Value from profile

another suggestion?
more cases that we should consider?

For clearness I would prefer heuristic 1) but I'm open for discussion.
Comment 20 Katarina Behrens (CIB) 2015-07-09 08:28:33 UTC
> how to represent and distinguish document specific FSFSR and default FSFSR
> settings in the GUI? I first thought, we could reuse the existing "global
> FSFSR" dialog and just make it document specific. Should we add another
> option "formula syntax for string references in new documents"? (have no
> better idea ATM)

So I've tried hard to come up with some answer to this question, unfortunately I've preciously few creative ideas. 

I wouldn't place document-specific setting into Tools -> Options. That's the place for global options and if any document-specific options currently happen to be there, it's an UX bug that needs to be fixed :o) imo :o)

I've looked into how other document-specific options are set in Calc/Impress/Writer and it's all in rather inconsistent state, the options are scattered around various menus and sub-menus, for example:

Calc -> Data -> Calculate -> Autocalculate
Calc -> View -> (most of the things there)
Impress -> Slide -> Display background on Master etc.

(and toggling some of those options doesn't even set document to modified, grrr ... but that's another bug)

So to be consistent with this inconsistency, maybe add another submenu, String Reference Syntax (to Data -> Calculate, or wherever appropriate) with radiobuttons (analogous to View -> Zoom, for example) with 3 options

(*) Calc A1
( ) Excel A1
( ) Excel R1C1

indicating what FSFSR of current document is. 

In the long run, it would be of course wonderful to have some config editor of document-specific settings -- much like File -> Properties ... -> Custom Properties ( that could be added as a new tab to File -> Properties ... dialog, or to some other relevant place)
Comment 21 m.a.riosv 2015-07-18 02:03:51 UTC
*** Bug 80237 has been marked as a duplicate of this bug. ***
Comment 22 Commit Notification 2015-07-20 11:22:04 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=01d1165572f53ca50c626fa036343932c1e8c5db

tdf#92256: ODF save/load syntax for string reference

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 23 Commit Notification 2015-07-20 12:33:35 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=aa354f2718b0ac81a6701b8c8ef41a15fe80c880

Related tdf#92256: map CONV_OOO to listbox item no.1

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 24 Commit Notification 2015-07-21 18:21:38 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=abe178814489286aa45dc0799df50e650a78bc9d

tdf#92256: Introducing CONV_A1_XL_A1 address pseudoconvention

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 25 Commit Notification 2015-07-23 16:26:49 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c2e48be7e54efd33ad4dae0c24185597521efd46

tdf#92256: OOXML save/load syntax for string reference

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 26 Commit Notification 2015-07-24 14:35:32 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=738be269bfc795400fcb10d4eefb6c33b17bded4

tdf#92256: Handle case when string ref syntax is unknown

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2015-07-25 00:58:18 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=fe88fe6f828597fe2645a20c1a07716216a810c3

tdf#92256: Make OOXML filter CONV_A1_XL_A1 aware too

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 28 Commit Notification 2015-08-12 12:55:01 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5945659d8ddc3ca6bb5912965e13d609a36ff003

tdf#92256: Make sure ref syntax of Excel docs gets saved

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 29 Commit Notification 2015-08-14 11:48:50 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2bfeabde75d1e99879465fee6fe6a97357739ae2

tdf#92256: Save ref syntax when different from native one

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 30 Commit Notification 2015-08-14 11:48:57 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=077c8838ace23cfe5c54ae222153dbef815b56b0

tdf#92256: Don't force CalcA1 syntax on all !Microsoft xlsx docs

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 31 Commit Notification 2015-08-14 11:49:03 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b0a890e5bda70afa77feb3e89c3d446b00e580a3

Some more tdf#92256 related tests

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 32 Commit Notification 2015-08-18 14:58:01 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=77cf7b7758dde33dac8e2e2edf0fbffd98af60e3&h=libreoffice-5-0

tdf#92256: Improved interop of INDIRECT function

It will be available in 5.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 33 Thorsten Behrens (CIB) 2015-08-20 23:41:35 UTC
That now looks pretty fixed to me.
Comment 34 Katarina Behrens (CIB) 2015-10-13 13:20:41 UTC
I hate to reopen this
Comment 35 Commit Notification 2015-10-16 22:17:09 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=313df729a3b6a94275ac992e74b18256fd286f93

tdf#92256, c#19: Implement "current doc only" switch

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 36 Commit Notification 2015-10-16 22:20:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0ac191565d541f66d4cc88ec54c8f3b5e03f0989

ignore "current document" values at module level, tdf#92256 follow-up

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 37 Commit Notification 2015-10-28 15:00:55 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=73a851048f5173c3e6411f48ef26e006de7b63d8

tdf#92256: Don't save CONV_UNSPECIFIED string ref syntax value

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 38 m.a.riosv 2015-11-02 12:19:38 UTC
*** Bug 95513 has been marked as a duplicate of this bug. ***
Comment 39 Elmar 2015-11-03 04:20:36 UTC
Thank you, @m.a.riosv, this is very helpful. (re duplicate 95513)

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.
Comment 40 Commit Notification 2015-11-16 19:54:39 UTC
Katarina Behrens committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c3293e0eaae39f189fd554a39c130738e107431e&h=libreoffice-5-0

tdf#92256: Don't save CONV_UNSPECIFIED string ref syntax value

It will be available in 5.0.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.