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.
Created attachment 116734 [details] Screenshot/behaviour with OOo 3.2.1
Created attachment 116735 [details] Screenshot/behaviour with LO 4.4
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..
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?
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.
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
(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?
(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.
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
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.
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.
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.
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.
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 ... )
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.
> 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 :)
(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.
(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.
(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.
> 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)
*** Bug 80237 has been marked as a duplicate of this bug. ***
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
That now looks pretty fixed to me.
I hate to reopen this
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.
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.
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.
*** Bug 95513 has been marked as a duplicate of this bug. ***
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.
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.
Created attachment 184534 [details] confirm fix in LO 7.6 Screenshot shows that gets expected result
@Elmar: please don't mess with the Assignee field, thanks.
(In reply to Eike Rathke from comment #42) > @Elmar: please don't mess with the Assignee field, thanks. Oops! What did I do wrong? Thought I was copied on this to verify if I pick up the bug or not.
See History, you assigned this bug to yourself. We keep assignee on the person working on it / who fixed the bug.