Bug 99986 - EDITING: formula use in LO and save as XSLX give error in excel (function IF accept invalid reference)
Summary: EDITING: formula use in LO and save as XSLX give error in excel (function IF ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.0 release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: XLSX-Corrupted Cell-Reference
  Show dependency treegraph
Reported: 2016-05-21 23:42 UTC by VLB
Modified: 2023-08-24 19:57 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

test file (8.06 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-05-21 23:42 UTC, VLB
test file XSLX (4.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-21 23:43 UTC, VLB

Note You need to log in before you can comment on or make changes to this bug.
Description VLB 2016-05-21 23:42:52 UTC
Created attachment 125219 [details]
test file

Wi 10 64 bits LO

I have a wrong formula, but in LO isn't a mention. When save as XSLX and open in excel give a error.

In cell A1 is the formula =ALS(A2<A3;$A$4;ALS($f5a$5<A6;7;4)) and is wrong.
The formula must =ALS(A2<A3;$A$4;ALS($a$5<A6;7;4))


1) open test file *.ods
2) save as XSLX
3) open XSLX file in excel and there is a error present
Comment 1 VLB 2016-05-21 23:43:25 UTC
Created attachment 125220 [details]
test file XSLX
Comment 2 raal 2016-05-22 06:24:37 UTC
Confirm. Function IF accept invalid reference. =IF(A2<A3;$A$4;IF($f5a$5<A6;7;4))

Build ID: eb7593daa4bac21bd68182c8bbbd3ee3bd7b64dd
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-05-03_07:06:45
and LibreOffice 3.5.0
Comment 3 GerardF 2016-05-22 06:46:29 UTC
If the 1st condition is met, the following part is not checked.
Change A2 from 1 to 3 gives a error.
Comment 4 QA Administrators 2017-05-22 13:41:11 UTC Comment hidden (obsolete)
Comment 5 VLB 2017-05-22 17:14:14 UTC
The bug is still present in LO (x64) win 10.
Comment 6 VLB 2017-12-20 14:54:09 UTC
The bug is still present in LO (x64) win 10.
Comment 7 QA Administrators 2019-01-29 03:48:15 UTC Comment hidden (obsolete)
Comment 8 VLB 2019-02-05 18:42:46 UTC
I have test in wi 10 LO (x64) and the bug is still present.
Comment 9 VLB 2019-10-22 17:43:15 UTC
I have test in wi 10 Versie: (x64)and the bug is still present.
Comment 10 QA Administrators 2023-01-05 03:20:24 UTC Comment hidden (obsolete)
Comment 11 Eike Rathke 2023-08-24 19:57:57 UTC
Code errors are evaluated during run time, not resulting in overall formula errors during compile time (with a few exceptions), hence if the faulty code is not executed in an IF() path no error occurs. Reason is that any error condition can be further evaluated using IFERROR() or ERROR.TYPE() or similar. If we stopped and set an error during compilation already that would not be possible or yield different results.

What we maybe could do is catching the name error during formula cell input already, like is done for missing operators and operands and such.