Created attachment 101365 [details]
sample excel sheet to import
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
=IF(IF($D2="ne";"";INDIRECT("Kontakty!A" & $D2))=0;"";IF($D2="ne";"";INDIRECT("Kontakty!A" & $D2)))
=IF(IF($D2="ne";"";INDIRECT("Kontakty.A" & $D2))=0;"";IF($D2="ne";"";INDIRECT("Kontakty.A" & $D2)))
Operating System: All
Version: 18.104.22.168 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.
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 ***