Summary: | Blank cells formatted as numbers not treated as ZERO when pulled via VLOOKUP to external file | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | Charles <tanstaafl> |
Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | NEW --- | ||
Severity: | normal | CC: | aron.budea, ilmari.lauhakangas, miguelangelrv, tanstaafl |
Priority: | medium | ||
Version: | 5.2.4.2 release | ||
Hardware: | x86-64 (AMD64) | ||
OS: | Windows (All) | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Bug Depends on: | |||
Bug Blocks: | 109329 | ||
Attachments: |
Source spreadsheet example
Management spreadsheet example Management Example Spreadsheet Screenshot of sort in Management sheet Screenshot of Sort in Source The two separate files combined into one |
Description
Charles
2017-01-18 15:02:16 UTC
Created attachment 130527 [details]
Source spreadsheet example
This is an example Source spreadsheet that, when used with the Management example also attached, exhibits the problem.
Created attachment 130528 [details]
Management spreadsheet example
This is an example Management spreadsheet that, when used with the Source example also attached, exhibits the problem.
Sorry for the conflicting names in the description vs the Steps to Reproduce - I decided to change the names of the spreadsheets while writing up the Steps to Reproduce when I realized that referencing the columns as A and B would be confusing if the spreadsheets were named A and B. Works fine for me, changing the name of the source file to A.ods Version: 5.2.5.1 (x64) Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22 CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; Locale: es-ES (es_ES); Calc: group Created attachment 130555 [details]
Management Example Spreadsheet
Oops, forgot to change the reference in the spreadsheet before uploading.
Created attachment 130556 [details]
Screenshot of sort in Management sheet
Screenshot of what it looks like sorted on column B in descendning order in the management sheet
Created attachment 130557 [details]
Screenshot of Sort in Source
And this is what it looks like after sorting on column B in descending order in the original/source sheet
(In reply to m.a.riosv from comment #4) > Works fine for me, changing the name of the source file to A.ods > > Version: 5.2.5.1 (x64) > Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22 > CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; > Locale: es-ES (es_ES); Calc: group Thanks for pointing out I forgot to change the reference after I changed the filenames... :) But - when you say 'works fine', are you saying you do not see the blank cells ABOVE the numeric ones in the Management sheet when sorted in DESCENDING order? I just attached screenshots of what the exact same sort looks like in each sheet. Sorting on Source doesn't matter for VLOOKUP() on Magnagement, last parameter is 0. Sorting on Magnagement only column B changes nothing for me. But by your image you are sorting also the column A with column B values base, see the letters on the last rows, so there is nothing wrong on the results. I think the better you can do is to create a pivot table from the magnagement A:B table, in which you can sort and do some calculation without need to touch the link formulas. Sorry, I don't understand your explanation. Of course I also included column A data in both sorts, otherwise the relationship with the other data in the row is scrambled. My apologies for not including this in the steps to reproduce the problem, I thought it was obvious. Highlight all data in both columns A and B, then sort on column B, and the sorts are very different when doing it from the Management sheet using VLOOKUP. So, please provide documentation or at least an explanation on why the use of VLOOKUP causes the blank cells to NOT be treated as zero, as they are in the Source sheet, or confirm this as a bug. Thanks Once the obvious explained I can see the issue. Looks the same problem than in tdf#103230, empty cells from linked files are not interpreted the same than those on the same file. *** This bug has been marked as a duplicate of bug 103230 *** No, not a duplicate of that one, using VLOOKUP when the two sheets are in t he same file, as is the case in the bug you duped this one to, works just fine. I will attach a 'Combined' example (just copied the management sheet to the source book and adjusted formulas) to demonstrate. Created attachment 130579 [details]
The two separate files combined into one
Copied the Management sheet into the Source spreadsheet, and adjusted the reference, to demonstrate that it works just fine when the formula is calling data from the same spreadsheet.
NEW per Miguel's confirmation. ** 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 with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. 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) from 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: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug Dear Charles, 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 with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. 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) from https://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: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug Dear Charles, 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 with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. 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) from https://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: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug |