Created attachment 117626 [details] Demonstration containing addenda A sibgle cell reference into a not accessible (or not existing e.g.) spreadsheet file (ods or Excel type e.g.) will correctly produce the error indicator #REF! (Range references may produce a misleading error message.) The actual bug(s): As soon as the missing reference is used as an operand it will be evaluated as a blank cell would be. It is evaluated to 0 or to the empty string depending on the operator, in case of comparison depending on the second operand. It even evaluates to 0 if used as a selecting parameter with OFFSET or INDEX. The missing reference passed to one of the information functions ISTEXT, ISNUMBER, ISLOGICAL, ISERROR will result in FALSE returned. ISBLANK on contrary will answer TRUE. Steps to reproduce the (basic) bug: 1. Create a new Calc document 2. Enter into A3 of the first sheet the formula ='file:///NonExistingFilePath/AnyName.ods'#$Sheet1.A$1 3. Verify the expected error message #REF! 4. Enter into A4 below =1+'file:///NonExistingFilePath/AnyName.ods'#$Sheet1.A$1 5. Verify the (unexpected) result 1 6. Enter into A12 =ISBLANK('file:///NonExistingFilePath/AnyName.ods'#$Sheet1.A$1) 7. Verify the (unexpected) result TRUE Expected behavior: In case of 4, #REF! or a compatible error message In case of 6. #REF! or a compatible error message The reported bug is demonstrated in the attached example. The attachment also contains a few addenda to this report.
I experienced the bug with v4.4.4 and verified it with V5.0.0.5. Meanwhile I also verified the same behaviour with V3.4.5 (portable). The "legacy" version 3.3.4. behaved in a slightly different way, but the basic bug reported was already present there.
Hi @Wolfang, thanks for reporting. And for a so detailed report. Reproducible with Win7x64 Version: 5.1.0.0.alpha1+ (x64) Build ID: bb697b4c7bd694c6b81ed2a4cc9471cb5573b592 TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-07-31_22:24:37 verified that as commented slightly different with 3.3.4. But doesn't seem inherited from AOo, at least in 4.1.1 seems to work fine. I think we can change the version to 3.3.4 LibreOffice 3.3.4 OOO330m19 (Build:401) tag libreoffice-3.3.4.1
Well, with v3.3.4 and Go-oo 3.2.0 on win7 x64 the =ISBLANK issue is FALSE. And with v3.4.4 the outcome in line 6 is still 1. With Go-oo 3.2.0 however, the outcome from line 4 and 6 are both #REF!
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=7de92ad48d8c4fe7a1f9fb24ef8afc7d8907788e tdf#93101 propagate external reference error from refcache token It will be available in 5.2.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=2f94ff566f7827792175daedb92f12a7e61ad95d tdf#93101 correctly resolve svExternalSingleRef token in GetMatrix() It will be available in 5.2.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=824cf015683383b553c744a746ac8e8ea65495cf tdf#93101 handle svExternalSingleRef in ISERROR() It will be available in 5.2.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.
Note that when loading the test case document it needs to be hard recalculated (Shift+Ctrl+F9) to display the now correct values. Also, the #ref!.A$1 in cell A18 is something different, in some older versions invalidated sheet references were stored in an undefined way which so far when reloading is not resolved to an invalid reference but to an invalid symbol instead.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f053086fbc625fca8ba7b956738d33ba78a80f9b handle svExternalSingleRef in all IS*() functions, tdf#93101 related It will be available in 5.2.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=365c4d8c60e89fd57a91f51ca51a8796fe91edd7 handle svExternalDoubleRef in all IS*() functions, tdf#93101 related It will be available in 5.2.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.
Pending review https://gerrit.libreoffice.org/25207 for 5-1
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=34e70369f571c78e4bb235470ce710f531e0d026 unit test for unresolved external references, tdf#93101 It will be available in 5.2.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 "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4c05c610661096342c0f826936aa3307a7ca78b3&h=libreoffice-5-1 Resolves: tdf#93101 handle external references and propagate error It will be available in 5.1.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.