Steps to reproduce: 1. Open the attached ods test file; 2. Observe that cell B5 and C4 in sheet2 have VLOOKUP formula, and are showing the value as blank. >> This is expected, as their source values (B5 and C4 in sheet1) are blank. 3. Click the auto filter icon in B1 or C1 of sheet2. >> The first value in the filer is a "blank box". This is wrong, the filter should never give this result. 4. Copy cell B5 or C4 of sheet2, and paste only value to another empty cell. >> the cell you pasted to is showing "0". This is wrong, as you see blank value in B5 and C4. Current Behaviour: (See the descriptions in the above steps) Expected: a. Filter result in step 3 should treat the blank value as blank. b. Pasted result in step 4 should be blank, rather than "0". OS: Windows XP SP3 Version: 4.3.0.1 Build ID: 67f5430184326974072b65403ef1d9d934fc4481 Note: In Microsoft Office 2010, VLOOKUP always return "0" if the source cell is blank. I believe this is their bug.
Created attachment 101390 [details] test file: vlookup-blank-value.ods P.S.: When Unzip the ods file and observe content.xml: In the sheet1 part, it shows the following for the blank cell: <table:table-cell /> But in the sheet2 part, it shows: - <table:table-cell table:style-name="ce2" table:formula="of:=VLOOKUP([.$A5];[$sheet1.$A$1:.$C$6];2;0)" office:value-type="float" office:value="0" calcext:value-type="float"> <text:p /> </table:table-cell>
Created attachment 101394 [details] test file: vlookup-blank-value.ods Please ignore the previous file.
Just my personal opinion: > 3. Click the auto filter icon in B1 or C1 of sheet2. > >> The first value in the filer is a "blank box". This is wrong, the filter should never give this result. I think it's ok so we could filter blank cells. > 4. Copy cell B5 or C4 of sheet2, and paste only value to another empty cell. > >> the cell you pasted to is showing "0". This is wrong, as you see blank value in B5 and C4. I agree, should be no value. Tested under LO 4.0.6.2 - Win7 x86
(In reply to comment #3) Thanks for testing. > > 3. Click the auto filter icon in B1 or C1 of sheet2. > > >> The first value in the filer is a "blank box". This is wrong, the filter should never give this result. > I think it's ok so we could filter blank cells. I don't think so. If you go to sheet1 and set autofilter on the data, you will see that in "normal" cases blank value are not shown in the filter results, but you can choose the "Empty" option located within "Top 10, Empty, Not Empty" to filter out the empty results.
> I don't think so. If you go to sheet1 and set autofilter on the data, you > will see that in "normal" cases blank value are not shown in the filter > results, but you can choose the "Empty" option located within "Top 10, > Empty, Not Empty" to filter out the empty results. You're right, if cell have no value so "Empty" option should be enough. Now I agree with you :)
(In reply to comment #5) @ign_christian: So do you reproduce this bug behaviour following my steps? If you can reproduce, please help to mark the status to NEW. Thanks!
@suokunlog, I confirm that behavior only in 4.0.6.2. Your report is against 4.3.0.1, so I think someone with 4.3.0.1 that have the right to set NEW ? I also think this report should be splitted. The auto filter icon looks like enhancement request.
(In reply to comment #7) > Your report is against 4.3.0.1, so I think someone with 4.3.0.1 that have > the right to set NEW ? 4.3.0.1 is the version I tested it, if you can reproduce in 4.0.6.2, then of couse the status can be set NEW, and the version should be changed to 4.0.6.2 accordingly to reflect the most early version this bug appears. > I also think this report should be splitted. The auto filter icon looks like > enhancement request. The filter results issue should be related to the VLOOKUP value issue, so I reported as one report. It's OK for me to file a saparate bug report if the devs think they are different issue. --> Set to NEW; --> Changed version to 4.0.6.2.
Also reproducible in Ubuntu 14.04 x86, 3.6.7 release. Set version to 3.6.7.2 and OS to ALL.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-07-18
Still confirmed. Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+ Build ID: fcc2415ade6ae93710bbbda9f7e163045e323105 TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-10-21_16:55:13 Locale: fi-FI (fi_FI)
The following issue is now working fine: > The first value in the filer is a "blank box". This is wrong, > the filter should never give this result. The following issue remains: > the cell you pasted to is showing "0". This is wrong, > as you see blank value in B5 and C4. Will report separate issue for this one. Mark as WORKSFORME.