Created attachment 93439 [details] ods file that shows the reported problem I was editing a spreadsheet created with previous versions of LibreOffice and modified with the previous version 4.1.4.2. The first sheet of the spreadsheet contains a summary table, of which cells are filled using the VLOOKUP function. Attempts to edit cells within the VLOOKUP range referenced by the function cause the entries in cells N614:N645 to display #N/A. The VLOOKUP function in cells N646 and below are still good but any attempts to restore the correct answer to the cells above, for example by pasting the formula (apparently identical) into the cells N614:N645, does not solve the problem. If I take the defective spreadsheet and open it in another machine running version 4.1.4.2., the problem is quickly resolved. A test file that showed this defect is attached.
Hi Richard, thanks for reporting. Reproducible with: Win7x64Ultimate. Version: 4.2.0.4 Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71 I can not find any set up in M/T/O/Lc Calculation or Formula that change the behaviour. As workaround, VLOOKUP() seems to works fine adding the fourth parameter with zero (unsorted data).
Created attachment 93696 [details] Another example showing VLOOKUP error VLOOKUP stops to work after upgrading Using portuguese version Version 4.2.0.4 ID de compilação: 420m0(Build:4) Ubuntu/Libreoffice installed from ppa-repository.
*** Bug 74777 has been marked as a duplicate of this bug. ***
Ok. I think I've found the pattern here. It happens when the top two cells in the search range are empty *and* the match is on the first non-empty cell.
It took me a while but I finally understand the reason for the failure and a proper fix for it. The bad news is that the fix will be a bit large since I had to re-implement the faluty binary search algorithm to make it functional again. The good news is that the same fix also fixes Bug 74558.
(In reply to comment #5) > The good news is that the same fix also fixes Bug 74558. I meant Bug 72348.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=888851bdd7498558af1a74f7e982ad1455ef3a35 fdo#74558: Add new test case to catch this regression. 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=377d749ff8211fa9e69d47a92810af9bc0652990 fdo#74558: Re-implement BinarySearch() to disregard empty cell blocks. 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.
4.2 backport request: https://gerrit.libreoffice.org/8042
*** Bug 72348 has been marked as a duplicate of this bug. ***
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ff53d15a6633b1188e97b8b15a71190f072afeea&h=libreoffice-4-2 fdo#74558: Re-implement BinarySearch() to disregard empty cell blocks. It will be available in LibreOffice 4.2.2. 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1c49a6b61200fef0f6263c074282b2127359bb15&h=libreoffice-4-2-1 fdo#74558: Re-implement BinarySearch() to disregard empty cell blocks. It will be available already in LibreOffice 4.2.1. 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.
Now it's fixed.
Hi folks, I just upgrade to Versão: 4.2.1.1 ID de compilação: 420m0(Build:1) Portuguese Version using the ppa version and the bug persists :( Just open the example that I uploaded that you can see the error.
Check with Bjoern and see if the PPA build contains this fix. Or wait for 4.2.2 to verify. The state of 4.2.1 build is a bit of a mess and it shouldn't be used to verify this.
@(In reply to comment #15) > Check with Bjoern and see if the PPA build contains this fix. Or wait for > 4.2.2 to verify. > > The state of 4.2.1 build is a bit of a mess and it shouldn't be used to > verify this. Checking the git log, the commit 1c49a6b61200fef0f6263c074282b2127359bb15 came in _after_ the 4.2.1.1 tag we released as 4.2.1 final, so this is indeed not in _any_ 4.2.1 release (neither the TDF builds, nor Ubuntu PPA builds). Adjusting target in Whiteboard Status as such. Automated Comment 12 is wrong. As Kohei says, we fasttracked 4.2.1 for some other issues and this unfortunately causes confusion for some automated tools.
Thanks Kohei. Verified in: Win7x64Ult. Version: 4.2.3.0.0+ Build ID: 7af1124f83df4d96f6ebe58536de7c51406df661 TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-02-20_11:39:39 Version: 4.3.0.0.alpha0+ Build ID: 22b709e84a7b6d38cab2dd37f2f2b28e0fc9d062 TinderBox: Win-x86@39, Branch:master, Time: 2014-02-20_00:01:31 Also for windows the bug is present in 4.2.1
correcting target, since 4.2.1.1 is 4.2.1 release