Bug 38343 - Behaviour of Advanced Filter in Calc/Spreadsheet is incorrect. Produced not working formulas
Summary: Behaviour of Advanced Filter in Calc/Spreadsheet is incorrect. Produced not w...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.0 release
Hardware: All All
: medium minor
Assignee: Not Assigned
: 89479 (view as bug list)
Depends on:
Blocks: Data-Filter
  Show dependency treegraph
Reported: 2011-06-15 05:12 UTC by Georg Hörmann
Modified: 2019-12-08 16:41 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

Screenshot of error message (400.74 KB, image/jpeg)
2011-06-15 05:12 UTC, Georg Hörmann
Test case for sorting and filtering with formula problem (16.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-11-22 08:45 UTC, sasha.libreoffice

Note You need to log in before you can comment on or make changes to this bug.
Description Georg Hörmann 2011-06-15 05:12:37 UTC
Created attachment 47994 [details]
Screenshot of error message


the behavior of the advanced filter is very strange. Please see the attached screenshot. An advanced filter copies the *formulas* and not the *values*, this leads to the error messages in the screenshot (columns UVW) because the formulas frequently loose their context after copying. In Excel only the values are copied - this is something I would also suggest for Libreoffice, also because imported xls files (like the one from the attached screenshot) produce errors when the filter is applied.
Comment 1 Björn Michaelsen 2011-12-23 12:22:08 UTC Comment hidden (obsolete)
Comment 2 Florian Reisinger 2012-08-14 13:58:28 UTC
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.


Comment 3 Florian Reisinger 2012-08-14 13:59:44 UTC Comment hidden (obsolete)
Comment 4 Florian Reisinger 2012-08-14 14:04:21 UTC Comment hidden (obsolete)
Comment 5 Florian Reisinger 2012-08-14 14:06:33 UTC Comment hidden (obsolete)
Comment 6 sasha.libreoffice 2012-09-18 08:33:05 UTC
problem with copying formulas instead of data reproduced in 3.3.4 and 3.6.1 on Fedora 64 bit in Data->Filter->Standard Filter and in Data->Filter->Advanced Filter

I agree that copying formulas instead of data is strange. And formulas become not working in result.
IMHO should added option to force copying results of formulas instead of formulas itself. Or problem with copying incorrect formulas should be fixed. Or changed default behaviour to copying results of formulas instead of formulas.
Comment 7 Joel Madero 2012-11-21 17:00:34 UTC
Can you attach a document and give precise steps on how to reproduce? I just did a really simple test and used a filter and it did copy the formulas but it copied them correctly so no #Null values were given. I think it makes more sense to have formulas copied/sorted correctly rather than just copy the value (many people would still want the formula).

Marking as NEEDINFO until document and steps are attached. Please reopen as UNCONFIRMED once this is done. Apologies for the long delay, we're working on a big backlog. Thanks for your patience
Comment 8 sasha.libreoffice 2012-11-22 08:45:37 UTC
Created attachment 70418 [details]
Test case for sorting and filtering with formula problem

This attachment contains explanations how to use Sort and Advanced filter and test results.
Function Sort mentioned here because it is more simply and more frequently used. But appears that suffers from the same problem. IMHO we may use it for testing instead of Advanced filter

Reproduced in 3.6.3 on RFR 17 64 bit
Expected: correct values or formulas
Actually: only zeros
Comment 9 mohican 2013-03-06 20:08:08 UTC
With respect to sorting, it may be the same issue as in bug 45146
Comment 10 Joel Madero 2013-05-15 02:29:07 UTC
I agree that the root of this is the same as 45146 - in general we would mark that one as a duplicate of this one as this one is older BUT since that one has already been confirmed, just going to mark this as a dupe.

@Georg - if you disagree please let us know

@mohican - thanks for pointing in the direction of that dupe, I was pretty sure I had confirmed a similar one at some point :)

*** This bug has been marked as a duplicate of bug 45146 ***
Comment 11 Jean-Baptiste Faure 2014-10-21 05:03:04 UTC
This bug is not a duplicate of bug 45146. Indeed, even if the status of bug 45146 is not clear, the behavior described in it changed with the changes in sorting with reference provided by bug 81309 and bug 81633.

This one is about copying formula instead of the data in advanced filter and nothing changed after bug 81309 in LO 4.4.

Set status back to NEW.

Best regards. JBF
Comment 12 fury110 2015-02-22 16:48:32 UTC Comment hidden (no-value)
Comment 14 raal 2015-02-22 18:20:52 UTC
*** Bug 89479 has been marked as a duplicate of this bug. ***
Comment 15 fury110 2015-02-22 18:56:02 UTC Comment hidden (no-value)
Comment 16 Joel Madero 2015-02-22 20:04:30 UTC
@ Fury - 

Couple points here:

1. First warning - next one you will be banned at least temporarily from using bugzilla for violating The Document Foundation bugzilla rules (swearing and being verbally abusive towards community members);

2. The priority/severity literally has no real impact on getting the bug resolved, we use it internally only for general guidance - setting this to an ultra mega blocker is not going to convince a volunteer to fix the bug. You have admitted there are workarounds - by definition if there is a workaround we usually place the bug as minor, if it prevents high quality work, it's a normal bug, if it crashes and results in loss of data, then it's major or above - clearly this does not result in loss of data or crashes.

3. For more information on how our FRIENDLY community works please read the following: http://joelmadero.wordpress.com/2014/10/11/user-expectations-and-the-reality-of-our-community/

4. I should have reverted it back to minor which I am doing now (and again, this literally has no impact on whether a volunteer looks at the bug or not, it's simply giving us accurate information as to what the bug does)

5. For more information feel free to join our chat (so long as you're going to be productive and not an interruption to our meritocratic community): http://webchat.freenode.net/?channels=libreoffice-qa

Comment 17 fury110 2015-02-23 22:27:05 UTC Comment hidden (no-value)
Comment 18 tommy27 2016-04-16 07:23:47 UTC Comment hidden (obsolete)
Comment 19 Jean-Baptiste Faure 2016-04-17 08:58:11 UTC
I am not sure if this is really a bug or only a misunderstanding of what are relative and absolute references. Indeed, in the test case provided by Sasha, if you change the references in B3:B10 from relative to absolute, then the filter works as expected.

@Markus, please could you give your developer point of view? Is it the intended behavior to copy the cell content (formula if it is a formula) instead of the value of the cell?

Best regards. JBF
Comment 20 QA Administrators 2018-12-07 03:47:28 UTC Comment hidden (obsolete)
Comment 21 Xavier Van Wijmeersch 2018-12-07 10:31:51 UTC
I agree with comment19 "I am not sure if this is really a bug or only a misunderstanding of what are relative and absolute references. Indeed, in the test case provided by Sasha, if you change the references in B3:B10 from relative to absolute, then the filter works as expected."

My opinion its a misunderstanding off relative and absolute references.

I think it can be closed as WFM

Best regards
Comment 22 QA Administrators 2019-12-08 03:45:47 UTC Comment hidden (obsolete)
Comment 23 Jean-Baptiste Faure 2019-12-08 16:41:05 UTC
According to comments #19 and #21, closing as WorksForMe. Please feel free to reopen if you disagree.

Best regards. JBF