Description: Please see title and steps to reproduce. Steps to Reproduce: 1. Copy this to a new Calc spreadsheet: Number 0 1 2 3 4 5 6 7 8 9 =NA() 2. Make sure =NA() was evaluated as a formula to produce #N/A 3. Using AutoFilter, deselect #N/A from the allowed values in "Number", but keep everything else selected Actual Results: The #N/A line remains visible Expected Results: The #N/A line should be hidden by the filter Reproducible: Always User Profile Reset: No Additional Info: Happened on Linux Mint 22
Repro. I am setting this report as NEW. In recent versions, the behavior of AutoFilter has been modified multiple times, so I am not sure what the expected behavior should really be regarding cells with formulas (vs their results).
Works correctly in LO 7.2.7.2. The AutoFilter does not hide the #N/A value since LO 7.3, probably in connection to one of the several changes introduced to the AutoFilter feature.
(In reply to ady from comment #1) > Repro. > > I am setting this report as NEW. In recent versions, the behavior of > AutoFilter has been modified multiple times, so I am not sure what the > expected behavior should really be regarding cells with formulas (vs their > results). As a user, I can tell you I expect AutoFilter to only care about values/result by default, and not formulas. Maybe there ought to a way to specifically filter by formula, but that's not related to this issue in any case.
It works fine with Version: 24.8.0.3 (X86_64) / LibreOffice Community Build ID: 0bdf1299c94fe897b119f97f3c613e9dca6be583 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded AND Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 85e4dc15d09dc3193870041b2814263971a27791 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded AND Version: 24.2.0.3 (X86_64) / LibreOffice Community Build ID: da48488a73ddd66ea24cf16bbc4f7b9c08e9bea1 CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded AND Version: 7.6.7.2 (X86_64) / LibreOffice Community Build ID: dd47e4b30cb7dab30588d6c79c651f218165e3c5 CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded The #N/A is not showed when it is not selected in the filter. At least on Windows I can not reproduce the issue in the mentioned versions.
Created attachment 196003 [details] AutoFilter does not hide "#N/A" (from formula) I created this file using: Version: 7.2.7.2 (x86) / LibreOffice Community Build ID: 8d71d29d553c0f7dcbfa38fbfda25ee34cce99a2 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL STR: 1. Open the attached file. 2. In the AutoFilter dropdown, uncheck the "#N/A" value > OK. In LO 7.2, the resulting filtered list hides the "#N/A" row/value. Since LO 7.3 (and newer), the resulting filtered list still shows the "#N/A" row/value.
One detail (minor correction)... In attachment 196003 [details], I (inaccurately) wrote: "AutoFilter has “#N/A” unchecked, but in LO 7.3 it is still shown on the resulting list." Actually, the AutoFilter in attachment 196003 [details] is set to show "All" values, and you have to uncheck the "#N/A" value, in order to show the result, as the STR in comment 5 says.
Created attachment 196005 [details] Sample file created with 24.8 Seems something in the file provokes the issue. The attached sample file works fine. Please test.
@Miguel, In attachment 196005 [details], you started the list with the number 1 (one). Would you please start the list with the number 0 (zero) as first item, and re-test in your LO? You also ended the list of numbers with number 10 (ten) instead of number 9 (nine) before the NA() function, but I think that detail is not relevant. TIA.
Reproducible now Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: f9b5dad09dbdd56ff064096695a946b03e9e2914 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
So, let's emphasize (in order for this to be solved): that number zero in the list makes a difference.
The order doesn't matter, but all single-digit numbers must be there. The following triggers the bug too: Number 8 4 7 3 1 #N/A 2 0 9 5 6 And even with other numbers or text mixed in: Number 8 4 7 Banana 200 3 1 #N/A 2 54 0 9 19 5 6 If all single-digit numbers are selected, then #N/A cannot be hidden.
Note that in my previous comment, #N/A must be replaced with the formula =NA() (or any formula that produces #N/A error). The literal text "#N/A" does not exhibit this behavior. And of course, as ady said, the number 0 must be present like all other single-digit numbers.
This seems to have begun at the below commit in bibisect repository/OS linux-64-7.3. Adding Cc: to Luboš Luňák ; Could you possibly take a look at this one? Thanks 4b5e9305334b30b0b8e7774951d034c963140ddc is the first bad commit commit 4b5e9305334b30b0b8e7774951d034c963140ddc Author: Jenkins Build User <tdf@pollux.tdf> Date: Thu Nov 25 14:47:58 2021 +0100 source 1d0cdc461c43f0ce0eda4961311a972edf9e78e2 125746: try to search efficiently with a query with many items (tdf#133867) | https://gerrit.libreoffice.org/c/core/+/125746
Allow me to doubt that Luboš will reply in a timely manner. Is there any developer that could be interested in taking a look at AutoFilter in Calc?