Created attachment 99715 [details] the test case ods file Description: In calc, if the VLOOKUP formular has an "#N/A" value (i.e., there is no match), when save as MSO 2010 (xlsx) format the cell shows blank rather than "#N/A". Steps to reproduce: 1. Save the attached ods test file as MSO 2007/2010 XLSX. 2. Reopen with calc. Current behaviour: Cell B9 is showing blank (empty) value, with the formular still in the cell. F9 to recalculated do not solve the problem. (For information, cell B11 with the formular "=NA()" is also showing "#N/A" when save as xlsx) Expected: Cell B9 should show value "#N/A". OS: ubuntu 13.10 LibreOffice Version: 4.3.0.0.beta1 Build ID: 2e39c7e59c8fc8b16a54c3d981dceef27fb0c07f
Hello I confirm on windows 7/64 with Version: 4.3.0.0.beta1 Build ID: 2e39c7e59c8fc8b16a54c3d981dceef27fb0c07f So I set status to New Regards Pierre-Yves
Hard recalc [shift+Ctrl+F9] does the calculation. https://help.libreoffice.org/Calc/Recalculate Set up the option in: Menu/Tools/LibreOffice calc/Formula - Recalculation on file load - Excel 2007 and newer - Always recalculate. also does the job.
Hi (In reply to comment #2) > Hard recalc [shift+Ctrl+F9] does the calculation. > https://help.libreoffice.org/Calc/Recalculate > Set up the option in: > Menu/Tools/LibreOffice calc/Formula - Recalculation on file load - Excel > 2007 and newer - Always recalculate. > also does the job. Yes of course, but that is not the question. The cell should display #N/A before recalculating. Regards Pierre-Yves
>Comment#3 Are you sure?, as I know only formulas with changes in their precedents are calculated with [F9] For me the bug is retrieve the #N/A value when loading. Regression from: Win7x64Ultimate Version: 4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a Last working for me.
(In reply to comment #4) > >Comment#3 > Are you sure? Yes The cell should display the same content *as when saving*. If the formula was the sum of a range (which is properly managed) I think it would be more obvious to you. Regards Pierre-Yves
This is only an IMPORT issue, no problem with the xlsx EXPORT, as when I open the saved xlsx file of step 2 in comment 1 with LibreOffice 3.6.7, cell B9 is showing "#N/A" as expected, but open with 4.3.0.0 beta2 and 4.2.5.2 cell B9 is showing blank. (should the version remain as 4.3.0 beta1, or should it be set to 4.2.5.2 ? 4.3.0 beta1 was released earlier than 4.2.5, althrough the version number is newer.
Created attachment 109010 [details] The saved xlsx file To reproduce, you just need to open this xlsx file to see whether B9 is showing blank or "#N/A". (#N/A is expected).
4d0729fe3ad0839300ea972b482263f4a2a87c88 is the first bad commit commit 4d0729fe3ad0839300ea972b482263f4a2a87c88 Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com> Date: Wed Nov 27 11:09:00 2013 +0000 source-hash-4ac9fa7a887d09edf7f1fc38f155a93cff30ac97 commit 4ac9fa7a887d09edf7f1fc38f155a93cff30ac97 Author: Norbert Thiebaud <nthiebaud@gmail.com> AuthorDate: Mon Nov 11 22:01:47 2013 -0600 Commit: Norbert Thiebaud <nthiebaud@gmail.com> CommitDate: Mon Nov 11 22:37:26 2013 -0600 canvas: include <> for external includes Change-Id: I63f1fe2f1dfc65b3e50e03e1d2ebb6256560bd5c # bad: [793dbf6f80f497dfe587d560d6257f42a24273f6] source-hash-1581b1fc3ac82a7bd62df968226e98604a4ca52d # good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932 git bisect start 'latest' 'oldest' # good: [8092559c5013969ebda017d79200463b9b975038] source-hash-fd84daf696a368c2c7561b5253b32a63ecdeca4a git bisect good 8092559c5013969ebda017d79200463b9b975038 # good: [0270ef1b76a6de423b30f7927362cc01c1a0fc38] source-hash-b1f7dd66b898b03cb4bd8d434b6370310ea95946 git bisect good 0270ef1b76a6de423b30f7927362cc01c1a0fc38 # skip: [ddb123cad22440994cd332d9985dd9558fd07e07] source-hash-647fb29f528b891a1c92846640f7865f5c1fbe7f git bisect skip ddb123cad22440994cd332d9985dd9558fd07e07 # skip: [9d357dc6201f7cd91448595e0a3f89dfdae81946] source-hash-2304beaca33c63b94df99cb827716f00ce259f9a git bisect skip 9d357dc6201f7cd91448595e0a3f89dfdae81946 # good: [ef72aa34cf4ee6399b192de28708d621c9680a50] source-hash-7e07a45500dcbb891a85f0bc9b7049cf4d50bba1 git bisect good ef72aa34cf4ee6399b192de28708d621c9680a50 # good: [2472598a0b04eef3038d56137f27dc6dc1edf9e5] source-hash-5050dfc73f194d1d59222cac72e69a917655d816 git bisect good 2472598a0b04eef3038d56137f27dc6dc1edf9e5 # skip: [b25c77bc097fa4cc84f59ebdd34011bafbb3a770] source-hash-660800d6f33a01ad53fc0f5717e1c33868440d2f git bisect skip b25c77bc097fa4cc84f59ebdd34011bafbb3a770 # skip: [b25c77bc097fa4cc84f59ebdd34011bafbb3a770] source-hash-660800d6f33a01ad53fc0f5717e1c33868440d2f git bisect skip b25c77bc097fa4cc84f59ebdd34011bafbb3a770 # good: [89c7af972191d30321e9503b6454e8b1570ee3e8] source-hash-0aa9ced531b8d85ad067c1d156a9708eea628d78 git bisect good 89c7af972191d30321e9503b6454e8b1570ee3e8 # good: [89c7af972191d30321e9503b6454e8b1570ee3e8] source-hash-0aa9ced531b8d85ad067c1d156a9708eea628d78 git bisect good 89c7af972191d30321e9503b6454e8b1570ee3e8 # skip: [8bd734b6642f44d6876b227c0678f2be60425082] source-hash-c1f416feda0ef4ef215ca587b9bac03659b48c7a git bisect skip 8bd734b6642f44d6876b227c0678f2be60425082 # bad: [9bce3024c675da4777cf206f8d913ebba8c7ed2b] source-hash-a1aea9418f860595641da8dcc971f656eaacf8a3 git bisect bad 9bce3024c675da4777cf206f8d913ebba8c7ed2b # bad: [9bce3024c675da4777cf206f8d913ebba8c7ed2b] source-hash-a1aea9418f860595641da8dcc971f656eaacf8a3 git bisect bad 9bce3024c675da4777cf206f8d913ebba8c7ed2b # good: [5ab5ade9fe4d1210ad3c08cadb699495c7f244e0] source-hash-a18a92b1b0c4ff591bd83f190f11c196ed676e86 git bisect good 5ab5ade9fe4d1210ad3c08cadb699495c7f244e0 # good: [5ab5ade9fe4d1210ad3c08cadb699495c7f244e0] source-hash-a18a92b1b0c4ff591bd83f190f11c196ed676e86 git bisect good 5ab5ade9fe4d1210ad3c08cadb699495c7f244e0 # bad: [196cdde47dac6d5c1a8d2bd7056cae26ee505575] source-hash-5b72931475de334acfe414eff8f3c9496a7477f6 git bisect bad 196cdde47dac6d5c1a8d2bd7056cae26ee505575 # bad: [4d0729fe3ad0839300ea972b482263f4a2a87c88] source-hash-4ac9fa7a887d09edf7f1fc38f155a93cff30ac97 git bisect bad 4d0729fe3ad0839300ea972b482263f4a2a87c88 # first bad commit: [4d0729fe3ad0839300ea972b482263f4a2a87c88] source-hash-4ac9fa7a887d09edf7f1fc38f155a93cff30ac97
I guess this bug, together with bug 79249, are caused by the "GPU Calc" implemention: http://cgit.freedesktop.org/libreoffice/core/log/?id=9589aa8393ffdb7483716cb98ef2e446a7083227&qt=range&q=4ac9fa7a887d09edf7f1fc38f155a93cff30ac97 http://cgit.freedesktop.org/libreoffice/core/log/?id=9589aa8393ffdb7483716cb98ef2e446a7083227&qt=range&q=5b72931475de334acfe414eff8f3c9496a7477f6
Seems to work in master. Please check a current daily build again. I think Eike fixed that bug some time ago. And anything that can be fixed by a hard recalc is not an issue with the OpenCL implementation. It just means that we screw up during cached value import.
Works for me with: Version: 4.4.0.1 Build ID: 1ba9640ddd424f1f535c75bf2b86703770b8cf6f Locale: zh_CN Mark as RESOLVED WORKSFORME.
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]