Description: I have a spreadsheet - 'A' that has data entered into it on a regular basis. The cells this data is entered into are formatted as Number>Currency (with no decimals). I have another spreadsheet - 'B' that Management uses that pulls some data from spreadsheet 'A' using VLOOKUP. When sorting the Source (A) spreadsheet in descending order, the blank cells are treated as zeros, so cells with values in them are sorted to the top, and all blank cells are sorted below them. But, when I sort the cells in the Management spreadsheet (B), the blank cells are sorted to the top, above the cells with values in them, and then those with values are properly sorted in descending order. I consider this a bug. Steps to Reproduce: 1. Create a spreadsheet - Source - with two columns, column A that has labels in it, and column B that has numeric data (formatted as Currency with no decimals) entered into some of the cells but not all. 2. Create a second spreadsheet - Management - identical to 'Source', but pulls the data for column B from column B in 'Source' spreadsheet using VLOOKUP on column A. 3. Sort Source spreadsheet on column B in descending order, note that the blank cells are treated as zeros, so cells with values in them are sorted to the top, and all blank cells are sorted below them. Actual Results: 4. Sort Management spreadsheet the same way, note that the blank cells are sorted to the top, above the cells with values in them, and then those with values are properly sorted in descending order. Expected Results: The step 4. sort should be identical to the step 3. sort. Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
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