Create a new, blank spreadsheet 1. Put 'a' in A1 and 'z' in A2 2. Put 'a' in C1 and 1 in D1 3. Put 'z' in C1048576 and 26 in D1048576 4. Put formula in B1 =VLOOKUP(A1,INDIRECT("C1:D1048576"),2,0) 5. Copy formula into B2, which should give =VLOOKUP(B1,INDIRECT("C1:D1048576"),2,0) Everything works fine, the first vlookup returns 1 and the second returns 26... then... 6. Delete Row 3 The vlookup in B2 now returns blank! Can anyone else replicate this behaviour and explain why? 7. Put 'z' in C2, then remove it The vlookup in B2 works again! This has also been observed in OOo 3.2.1 using row 65536 instead of 1048576.
p.s. Why does OOo not allow C:D as a range, whereas Google Docs does? Is Google breaking the ODF standard by allowing this?
@Phil Hibbs: May I ask you to read <http://wiki.documentfoundation.org/BugReport>? Then please: - complete information (your OS, LibO version, ...) - Attach a sample document representing your step 5
Created attachment 40805 [details] Document at Step 5 of my explanation Document at Step 5 of my explanation.
Created attachment 40806 [details] Document after Step 6 The bug is not apparent in this file. Immediately after performing Step 6, the bug is apparent. However, on saving and closing and reopening the file, the bug is not apparent. There may be some clues in the saved file though so I have uploaded it just in case. Currently downloading Beta3 to test it in that.
Confirming that it is still observed in Beta3 using the "Step 5" attachment.
Both sample documents "indirectbug1.ods" and "indirectbug2.ods" look identical when I open them with my "LibreOffice 3.3.0Beta3 - WIN XP DE [OOO330m12 (build 3.2.99.3)]". Reproducible with "indirectbug1.ods", when I delete row 3 value in B2 disappears. Will not reappear with <f9>. Safe, close and reopen heals the problem. It seems that the problem is only reproducible with fond value in last row of the sheet. The problem was not reproducible when I saved / reopened the document and the found values were in last but one row Only a very vague suspect: this problem might have common roots with the one in Bug 31939? @Phil Hibbs: You also observed that with Beta2?
This is my area.
Dear bug submitter! Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs. To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem. Yours! Florian
reproduced in 3.6.0rc on Fedora 64 bit
Modified OS due to comment before. Effect is [Reproducible] with "LibreOffice 3.6.1.1 German UI/Locale [Build-ID: 4db6344] on German WIN7 Home Premium (64bit), but I know too few to understand it. But I am pretty sure that it's a bug. Indeed AOOo 3.4 and LibO 3.3.0 show the same problem, so problem seems inherited form OOo. Still [Reproducible] with parallel installation of Master "LOdev 3.7.0.0.alpha0+ - WIN7 Home Premium (64bit) ENGLISH UI [Build ID: 6900781]" (tinderbox: 2008R2@20, pull time 2012-08-14 09:27:23)
Please read this message in its entirety before responding. Your bug was confirmed at least 1 year ago and has not had any activity on it for over a year. Your bug is still set to NEW which means that it is open and confirmed. It would be nice to have the bug confirmed on a newer version than the version reported in the original report to know that the bug is still present -- sometimes a bug is inadvertently fixed over time and just never closed. If you have time please do the following: 1) Test to see if the bug is still present on a currently supported version of LibreOffice (preferably 4.2 or newer). 2) If it is present please leave a comment telling us what version of LibreOffice and your operating system. 3) If it is NOT present please set the bug to RESOLVED-WORKSFORME and leave a short comment telling us your version and Operating System Please DO NOT 1) Update the version field 2) Reply via email (please reply directly on the bug tracker) 3) Set the bug to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Bug is still present. Libre Office version: 4.3.2.2 Operating System: Windows 7 Enterprise SP1 I installed LiO fresh from the web site onto a new Windows 7 VM, followed my original instructions on a new blank workbook, and experienced the same behaviour. The formula in B2 stops working (returns blank instead of 26) when row 3 is deleted.
The same thing happens if I delete row 3 first and then type in the formula with the INDIRECT function call - any row beyond the row that was deleted is not found by the VLOOKUP. While experimenting, I have also managed to get it to return the wrong result. The A column contains "h", the C and D columns on Row 6 contain "h" and 7 respectively, but the VLOOKUP in the B column returns 8. I will upload a screenshot showing the problem, as saved files ODS do not exhibit the problem when re-opened.
Created attachment 108347 [details] Screenshot of an incorrect result being returned
The same behaviour is also observed with the OFFSET function instead of INDIRECT in step 4: 4. Put formula in B1 =VLOOKUP(A1,OFFSET($C$1,0,0,1048576,2),2,0)
Created attachment 108349 [details] Step 1 for incorrect result demonstration I have isolated a set of steps that result in the WRONG value being returned, which I consider much more serious than a blank value (it would be embarrassing to send out an invoice with a blank value, but if I invoice a customer with an incorrect value then that would be much worse). 1. Load the attached file 2. Delete row 5 3. Observe that the result in B3 for the VLOOKUP of "g" changes from 7 to 8, which is the value that "h" would have returned As with the earlier symptoms, saving the file after the delete and re-loading does not exhibit the problem. It occurs to me that the root cause of the problem appears to be that the results of the VLOOKUP are being held in the form of a cell reference, i.e. the VLOOKUP of "g" is stored in the form of "The contents of Cell D7", and the deletion of the row is not causing this cell reference to be re-assessed.
Confirming that this bug is still present in 4.4.0.3, both the original example that returns a blank, and the example from comment 19 that returns the wrong value from a VLOOKUP.
Also occurs in OOo 3.3.0 -> Version: Inherited from OOo
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5e83f49e5d62587f427ad416f5188ce81506c05b Resolves: tdf#31577 volatile lookup ranges must not be cached It will be available in 5.1.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-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f61d873202ddc30b7153e837d071d14dca07e3f4&h=libreoffice-5-0 Resolves: tdf#31577 volatile lookup ranges must not be cached It will be available in 5.0.0.3. 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 for 4-4 https://gerrit.libreoffice.org/16598
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8b26f758d5011b8b769fa171011210262683dd16&h=libreoffice-4-4 Resolves: tdf#31577 volatile lookup ranges must not be cached It will be available in 4.4.5. 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.
Notes for unit test writers: Revert has to be done manually. Possibly something to add into sc/qa/unit/data/functions/spreadsheet/fods/vlookup2.fods