Created attachment 101365 [details] sample excel sheet to import Problem description: references to other sheets doesn't import correctly (i get excel-like Sheet!A1 instead of libreoffice Sheet.A1) Steps to reproduce: 1. open the attached file in libreoffice 2. look at list SEARCH, E2 Current behavior: =IF(IF($D2="ne";"";INDIRECT("Kontakty!A" & $D2))=0;"";IF($D2="ne";"";INDIRECT("Kontakty!A" & $D2))) Expected behavior: =IF(IF($D2="ne";"";INDIRECT("Kontakty.A" & $D2))=0;"";IF($D2="ne";"";INDIRECT("Kontakty.A" & $D2))) Operating System: All Version: 4.2.4.2 release
Hi Pavel, thanks for reporting. I think there is not a bug. I don't think we could expected calc is going to modify a literal inside quotes. When it must or when not? There are other reported bugs about it with some options to get it working. https://bugs.freedesktop.org/show_bug.cgi?id=62732 https://bugs.freedesktop.org/show_bug.cgi?id=75773
Hi m.a.riosv, while I see the logic behind your reasoning and I see there are workarounds possible, I still consider this inconsistent behaviour, hence a bug. My reasoning goes as follows: - a nonliteral gets contverted nicely (see column D, sheet SEARCH), a literal not - the choice when to convert the literal or not could/should be based on wether the literal is a parameter to a function such as indirect(), which evaluates into a data refference. I can see how this might end pretty convoluted given the fact that the literal itself can be passed by too many references but you actually point out a solution (the bugs and the workaround with setting the formula syntax). If the import were to open the file as if Menu/Tools/Option/LibreOffice calc/Formula/Formula options - formula syntax were set to the excel standard (which is to be expected when opening a MSOffice generated file) but then changed to LibreOffice standard with the requirement of obtaining the same values after evaluating all functions, one should get the expected behaviour.
IMO it's not a good idea use literals in this way, I always try to avoid it. ut in any case there is the option to convert the bug in a request for enhancement, selecting it in status-importance.
May be, but as long as I have to work with spreadsheets from different sources, I'd pretty much appreciate if imports "just worked". Either way, thanks for the explanation, I wouldn't have figured out why there's this inconsistency between literals and nonliterals (didn't even notice the rule when the references translate right and when wrong). I guess it must be pretty confusing behaviour for others too, so I think that this really should be fixed / enhanced.
Summary: Fix typo
Comment on attachment 101365 [details] sample excel sheet to import Fix attachment mimetype
(In reply to Pavel from comment #0) > Problem description: > > references to other sheets doesn't import correctly (i get excel-like > Sheet!A1 instead of libreoffice Sheet.A1) What's the enhancement request here? The summary reads like a bug, not like a proposed enhancement. Status -> NEEDINFO (please change Status back to UNCONFIRMED after replying)
The issue is taked in https://bugs.documentfoundation.org/show_bug.cgi?id=92256 *** This bug has been marked as a duplicate of bug 92256 ***