tdf#117276 sc: Show hidden filter elements as inactive elements

Showing hidden values in the autofilter dropdown (as inactive when
it was hidden by another row) - without changing the behaviour of
autofilter. First those which belongs to non-hidden rows, then those
which belongs to hidden rows.

TODO: maybe we can add a global option where the user can switch on/off this feature.

Change-Id: Iafeb43176efe7ab422b22697d399c68c95d0319d
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/136595
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

16 files changed, 137 insertions, 48 deletions

diff --git a/sc/inc/column.hxx b/sc/inc/column.hxx
index 9303575a5d9d..56aa1f9fc65e 100644
--- a/sc/inc/column.hxx
+++ b/sc/inc/column.hxx
@@ -204,6 +204,7 @@ class ScColumn : protected ScColumnData
     SCTAB           nTab;
 
     bool mbFiltering : 1; // it is true if there is a filtering in the column
+    bool mbFilteredRow : 1; // it is true if the actual row of the filtered column is hidden
     bool mbEmptyBroadcastersPending : 1; // a broadcaster not removed during EnableDelayDeletingBroadcasters()
 
 friend class ScDocument;                    // for FillInfo
@@ -255,6 +256,7 @@ public:
     SCTAB GetTab() const { return nTab; }
     SCCOL GetCol() const { return nCol; }
     bool HasFiltering() const { return mbFiltering; }
+    bool IsFilteredRow() const { return mbFilteredRow; }
     sc::CellStoreType& GetCellStore() { return maCells; }
     const sc::CellStoreType& GetCellStore() const { return maCells; }
     sc::CellTextAttrStoreType& GetCellAttrStore() { return maCellTextAttrs; }
@@ -598,7 +600,7 @@ public:
 
     void GetFilterEntries(
         sc::ColumnBlockConstPosition& rBlockPos, SCROW nStartRow, SCROW nEndRow,
-        ScFilterEntries& rFilterEntries, bool bFiltering );
+        ScFilterEntries& rFilterEntries, bool bFiltering, bool bHiddenRow = false );
 
     bool GetDataEntries( SCROW nRow, std::set<ScTypedStrData>& rStrings) const;
 
diff --git a/sc/inc/typedstrdata.hxx b/sc/inc/typedstrdata.hxx
index 7918b5501fca..25406d0e5f52 100644
--- a/sc/inc/typedstrdata.hxx
+++ b/sc/inc/typedstrdata.hxx
@@ -26,22 +26,29 @@ public:
     };
 
     ScTypedStrData(
-        OUString&& rStr, double fVal = 0.0, double fRVal = 0.0, StringType nType = Standard, bool bDate = false ) :
+        OUString&& rStr, double fVal = 0.0, double fRVal = 0.0, StringType nType = Standard, bool bDate = false, bool bIsHiddenByFilter = false ) :
         maStrValue(std::move(rStr)),
         mfValue(fVal),
         mfRoundedValue(fRVal),
         meStrType(nType),
-        mbIsDate( bDate ) {}
+        mbIsDate( bDate ),
+        mbIsHiddenByFilter(bIsHiddenByFilter) {}
 
     ScTypedStrData( const OUString& rStr, double fVal = 0.0, double fRVal = 0.0, StringType eType = Standard,
-                    bool bDate = false );
+                    bool bDate = false, bool bIsHiddenByFilter = false );
 
     bool IsDate() const { return mbIsDate;}
+    bool IsHiddenByFilter() const { return mbIsHiddenByFilter;}
     const OUString& GetString() const { return maStrValue;}
     StringType GetStringType() const { return meStrType;}
     double GetValue() const { return mfValue; }
     double GetRoundedValue() const { return mfRoundedValue; }
 
+    struct LessHiddenRows
+    {
+        bool operator() (const ScTypedStrData& left, const ScTypedStrData& right) const;
+    };
+
     struct LessCaseSensitive
     {
         bool operator() (const ScTypedStrData& left, const ScTypedStrData& right) const;
@@ -70,6 +77,7 @@ private:
     double mfRoundedValue; // rounded value by format code
     StringType meStrType;
     bool   mbIsDate;
+    bool   mbIsHiddenByFilter;
 };
 
 class FindTypedStrData
diff --git a/sc/qa/uitest/autofilter/autofilter.py b/sc/qa/uitest/autofilter/autofilter.py
index f892d2f901a8..8f6db292230e 100644
--- a/sc/qa/uitest/autofilter/autofilter.py
+++ b/sc/qa/uitest/autofilter/autofilter.py
@@ -237,7 +237,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(5, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (5 active + 3 inactive)
+            self.assertEqual(8, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
@@ -245,7 +246,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(3, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (3 active + 9 inactive)
+            self.assertEqual(12, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
@@ -258,7 +260,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(9, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (9 active + 3 inactive)
+            self.assertEqual(12, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
@@ -279,7 +282,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(3, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (3 active + 5 inactive)
+            self.assertEqual(8, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
@@ -287,7 +291,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(4, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (4 active + 8 inactive)
+            self.assertEqual(12, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
@@ -300,7 +305,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(3, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (3 active + 1 inactive)
+            self.assertEqual(4, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
@@ -326,7 +332,8 @@ class AutofilterTest(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xTreeList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(2, len(xTreeList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (2 active + 1 inactive)
+            self.assertEqual(3, len(xTreeList.getChildren()))
             xOkBtn = xFloatWindow.getChild("cancel")
             xOkBtn.executeAction("CLICK", tuple())
 
diff --git a/sc/qa/uitest/autofilter2/tdf117276_autofilter_reset.py b/sc/qa/uitest/autofilter2/tdf117276_autofilter_reset.py
index 13ddda5bfa23..66279d18217d 100644
--- a/sc/qa/uitest/autofilter2/tdf117276_autofilter_reset.py
+++ b/sc/qa/uitest/autofilter2/tdf117276_autofilter_reset.py
@@ -47,8 +47,10 @@ class tdf117276_autofilter_reset(UITestCase):
         self.assertTrue(is_row_hidden(document, 4))
         self.assertFalse(is_row_hidden(document, 5))
 
-        self.assertEqual(1, self.get_values_count_in_AutoFilter(xGridWindow, "0"))
-        self.assertEqual(2, self.get_values_count_in_AutoFilter(xGridWindow, "1"))
+        # since tdf#117267, we are showing the hidden filter rows as inactive elements (1 active + 4 inactive)
+        self.assertEqual(5, self.get_values_count_in_AutoFilter(xGridWindow, "0"))
+        # since tdf#117267, we are showing the hidden filter rows as inactive elements (2 active + 1 inactive)
+        self.assertEqual(3, self.get_values_count_in_AutoFilter(xGridWindow, "1"))
 
     def test_run(self):
         with self.ui_test.create_doc_in_start_center("calc") as document:
@@ -135,7 +137,8 @@ class tdf117276_autofilter_reset(UITestCase):
             self.assertFalse(is_row_hidden(document, 5))
 
             self.assertEqual(5, self.get_values_count_in_AutoFilter(xGridWindow, "0"))
-            self.assertEqual(2, self.get_values_count_in_AutoFilter(xGridWindow, "1"))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (2 active + 1 inactive)
+            self.assertEqual(3, self.get_values_count_in_AutoFilter(xGridWindow, "1"))
 
             # 4. open filter of column B and deselect "Unique b5"
             xGridWindow.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "1", "ROW": "0"}))
diff --git a/sc/qa/uitest/autofilter2/tdf140754.py b/sc/qa/uitest/autofilter2/tdf140754.py
index 49115513ec65..10ffcd5ec75a 100644
--- a/sc/qa/uitest/autofilter2/tdf140754.py
+++ b/sc/qa/uitest/autofilter2/tdf140754.py
@@ -46,7 +46,8 @@ class tdf140754(UITestCase):
 
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(25, len(xList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (25 active + 140 inactive)
+            self.assertEqual(165, len(xList.getChildren()))
 
             # Without the fix in place, this test would have crashed here
             xOkBtn = xFloatWindow.getChild("ok")
@@ -65,7 +66,8 @@ class tdf140754(UITestCase):
 
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(10, len(xList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (10 active + 35 inactive)
+            self.assertEqual(45, len(xList.getChildren()))
 
             xOkBtn = xFloatWindow.getChild("ok")
             xOkBtn.executeAction("CLICK", tuple())
diff --git a/sc/qa/uitest/autofilter2/tdf141946.py b/sc/qa/uitest/autofilter2/tdf141946.py
index 6cbf7cc50404..0a934f9deeeb 100644
--- a/sc/qa/uitest/autofilter2/tdf141946.py
+++ b/sc/qa/uitest/autofilter2/tdf141946.py
@@ -50,7 +50,8 @@ class tdf141946(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(2, len(xList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (2 active + 1 inactive)
+            self.assertEqual(3, len(xList.getChildren()))
             xCloseBtn = xFloatWindow.getChild("cancel")
             xCloseBtn.executeAction("CLICK", tuple())
 
diff --git a/sc/qa/uitest/autofilter2/tdf48025.py b/sc/qa/uitest/autofilter2/tdf48025.py
index 2e4c4a1a8c08..e87b7c31b5e2 100644
--- a/sc/qa/uitest/autofilter2/tdf48025.py
+++ b/sc/qa/uitest/autofilter2/tdf48025.py
@@ -63,7 +63,8 @@ class tdf48025(UITestCase):
             xFloatWindow = self.xUITest.getFloatWindow()
             xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
             xList = xCheckListMenu.getChild("check_list_box")
-            self.assertEqual(2, len(xList.getChildren()))
+            # since tdf#117267, we are showing the hidden filter rows as inactive elements (2 active + 1 inactive)
+            self.assertEqual(3, len(xList.getChildren()))
             xCloseBtn = xFloatWindow.getChild("cancel")
             xCloseBtn.executeAction("CLICK", tuple())
 
diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx
index 91d5c4eaef2e..ebbefb03b714 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -2559,7 +2559,7 @@ class FilterEntriesHandler
 
         if (rCell.hasString())
         {
-            mrFilterEntries.push_back(ScTypedStrData(std::move(aStr)));
+            mrFilterEntries.push_back(ScTypedStrData(std::move(aStr), 0.0, 0.0, ScTypedStrData::Standard, false, mrColumn.IsFilteredRow()));
             return;
         }
 
@@ -2617,9 +2617,9 @@ class FilterEntriesHandler
         }
         // store the formatted/rounded value for filtering
         if ((nFormat % SV_COUNTRY_LANGUAGE_OFFSET) != 0 && !bDate)
-            mrFilterEntries.push_back(ScTypedStrData(std::move(aStr), fVal, rColumn.GetDoc().RoundValueAsShown(fVal, nFormat), ScTypedStrData::Value, bDate));
+            mrFilterEntries.push_back(ScTypedStrData(std::move(aStr), fVal, rColumn.GetDoc().RoundValueAsShown(fVal, nFormat), ScTypedStrData::Value, bDate, mrColumn.IsFilteredRow()));
         else
-            mrFilterEntries.push_back(ScTypedStrData(std::move(aStr), fVal, fVal, ScTypedStrData::Value, bDate));
+            mrFilterEntries.push_back(ScTypedStrData(std::move(aStr), fVal, fVal, ScTypedStrData::Value, bDate, mrColumn.IsFilteredRow()));
     }
 
 public:
@@ -2670,9 +2670,10 @@ public:
 
 void ScColumn::GetFilterEntries(
     sc::ColumnBlockConstPosition& rBlockPos, SCROW nStartRow, SCROW nEndRow,
-    ScFilterEntries& rFilterEntries, bool bFiltering )
+    ScFilterEntries& rFilterEntries, bool bFiltering, bool bHiddenRow )
 {
     mbFiltering = bFiltering;
+    mbFilteredRow = bHiddenRow;
     FilterEntriesHandler aFunc(*this, rFilterEntries);
     rBlockPos.miCellPos =
         sc::ParseAll(rBlockPos.miCellPos, maCells, nStartRow, nEndRow, aFunc, aFunc);
diff --git a/sc/source/core/data/documen3.cxx b/sc/source/core/data/documen3.cxx
index 77afc2ff7d94..ecc93881c0bf 100644
--- a/sc/source/core/data/documen3.cxx
+++ b/sc/source/core/data/documen3.cxx
@@ -87,6 +87,10 @@ void sortAndRemoveDuplicates(std::vector<ScTypedStrData>& rStrings, bool bCaseSe
         std::vector<ScTypedStrData>::iterator it =
             std::unique(rStrings.begin(), rStrings.end(), ScTypedStrData::EqualCaseSensitive());
         rStrings.erase(it, rStrings.end());
+        if (std::find_if(rStrings.begin(), rStrings.end(),
+            [](ScTypedStrData& rString) { return rString.IsHiddenByFilter(); }) != rStrings.end()) {
+            std::sort(rStrings.begin(), rStrings.end(), ScTypedStrData::LessHiddenRows());
+        }
     }
     else
     {
@@ -94,6 +98,10 @@ void sortAndRemoveDuplicates(std::vector<ScTypedStrData>& rStrings, bool bCaseSe
         std::vector<ScTypedStrData>::iterator it =
             std::unique(rStrings.begin(), rStrings.end(), ScTypedStrData::EqualCaseInsensitive());
         rStrings.erase(it, rStrings.end());
+        if (std::find_if(rStrings.begin(), rStrings.end(),
+            [](ScTypedStrData& rString) { return rString.IsHiddenByFilter(); }) != rStrings.end()) {
+            std::sort(rStrings.begin(), rStrings.end(), ScTypedStrData::LessHiddenRows());
+        }
     }
 }
 
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index 391d012344b7..52b4a0500a46 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -3000,7 +3000,11 @@ void ScTable::GetFilteredFilterEntries(
     {
         if (queryEvaluator.ValidQuery(j))
         {
-            aCol[nCol].GetFilterEntries(aBlockPos, j, j, rFilterEntries, bFiltering);
+            aCol[nCol].GetFilterEntries(aBlockPos, j, j, rFilterEntries, bFiltering, false/*bHiddenRow*/);
+        }
+        else
+        {
+            aCol[nCol].GetFilterEntries(aBlockPos, j, j, rFilterEntries, bFiltering, true/*bHiddenRow*/);
         }
     }
 }
diff --git a/sc/source/core/tool/typedstrdata.cxx b/sc/source/core/tool/typedstrdata.cxx
index 83ecdfa64053..953035724d45 100644
--- a/sc/source/core/tool/typedstrdata.cxx
+++ b/sc/source/core/tool/typedstrdata.cxx
@@ -12,19 +12,33 @@
 
 #include <unotools/collatorwrapper.hxx>
 
+bool ScTypedStrData::LessHiddenRows::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const
+{
+    return left.mbIsHiddenByFilter < right.mbIsHiddenByFilter;
+}
+
 bool ScTypedStrData::LessCaseSensitive::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const
 {
     if (left.meStrType != right.meStrType)
         return left.meStrType < right.meStrType;
 
     if (left.meStrType == Value)
+    {
+        if (left.mfValue == right.mfValue)
+            return left.mbIsHiddenByFilter < right.mbIsHiddenByFilter;
         return left.mfValue < right.mfValue;
+    }
 
     if (left.mbIsDate != right.mbIsDate)
         return left.mbIsDate < right.mbIsDate;
 
-    return ScGlobal::GetCaseCollator().compareString(
-        left.maStrValue, right.maStrValue) < 0;
+    sal_Int32 nEqual = ScGlobal::GetCaseCollator().compareString(
+        left.maStrValue, right.maStrValue);
+
+    if (!nEqual)
+        return left.mbIsHiddenByFilter < right.mbIsHiddenByFilter;
+
+    return nEqual < 0;
 }
 
 bool ScTypedStrData::LessCaseInsensitive::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const
@@ -33,13 +47,22 @@ bool ScTypedStrData::LessCaseInsensitive::operator() (const ScTypedStrData& left
         return left.meStrType < right.meStrType;
 
     if (left.meStrType == Value)
+    {
+        if (left.mfValue == right.mfValue)
+            return left.mbIsHiddenByFilter < right.mbIsHiddenByFilter;
         return left.mfValue < right.mfValue;
+    }
 
     if (left.mbIsDate != right.mbIsDate)
         return left.mbIsDate < right.mbIsDate;
 
-    return ScGlobal::GetCollator().compareString(
-        left.maStrValue, right.maStrValue) < 0;
+    sal_Int32 nEqual = ScGlobal::GetCollator().compareString(
+        left.maStrValue, right.maStrValue);
+
+    if (!nEqual)
+        return left.mbIsHiddenByFilter < right.mbIsHiddenByFilter;
+
+    return nEqual < 0;
 }
 
 bool ScTypedStrData::EqualCaseSensitive::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const
@@ -79,12 +102,13 @@ bool ScTypedStrData::operator< (const ScTypedStrData& r) const
 }
 
 ScTypedStrData::ScTypedStrData(
-    const OUString& rStr, double fVal, double fRVal, StringType nType, bool bDate ) :
+    const OUString& rStr, double fVal, double fRVal, StringType nType, bool bDate, bool bIsHiddenByFilter ) :
     maStrValue(rStr),
     mfValue(fVal),
     mfRoundedValue(fRVal),
     meStrType(nType),
-    mbIsDate( bDate ) {}
+    mbIsDate( bDate ),
+    mbIsHiddenByFilter(bIsHiddenByFilter) {}
 
 FindTypedStrData::FindTypedStrData(const ScTypedStrData& rVal, bool bCaseSens) :
     maVal(rVal), mbCaseSens(bCaseSens) {}
diff --git a/sc/source/ui/cctrl/checklistmenu.cxx b/sc/source/ui/cctrl/checklistmenu.cxx
index 833857614e72..36c2f891fb24 100644
--- a/sc/source/ui/cctrl/checklistmenu.cxx
+++ b/sc/source/ui/cctrl/checklistmenu.cxx
@@ -450,6 +450,7 @@ ScCheckListMenuControl::Config::Config() :
 ScCheckListMember::ScCheckListMember()
     : mnValue(0.0)
     , mbVisible(true)
+    , mbHiddenByOtherFilter(false)
     , mbDate(false)
     , mbLeaf(false)
     , mbValue(false)
@@ -719,6 +720,7 @@ namespace
             aLabel = ScResId(STR_EMPTYDATA);
         rView.set_toggle(rIter, bChecked ? TRISTATE_TRUE : TRISTATE_FALSE);
         rView.set_text(rIter, aLabel, 0);
+        rView.set_sensitive(rIter, !rMember.mbHiddenByOtherFilter);
     }
 }
 
@@ -727,7 +729,8 @@ IMPL_LINK_NOARG(ScCheckListMenuControl, EdModifyHdl, weld::Entry&, void)
     OUString aSearchText = mxEdSearch->get_text();
     aSearchText = ScGlobal::getCharClass().lowercase( aSearchText );
     bool bSearchTextEmpty = aSearchText.isEmpty();
-    size_t n = maMembers.size();
+    size_t nEnableMember = std::count_if(maMembers.begin(), maMembers.end(),
+        [](const ScCheckListMember& rLMem) { return !rLMem.mbHiddenByOtherFilter; });
     size_t nSelCount = 0;
 
     // This branch is the general case, the other is an optimized variant of
@@ -738,7 +741,7 @@ IMPL_LINK_NOARG(ScCheckListMenuControl, EdModifyHdl, weld::Entry&, void)
 
         bool bSomeDateDeletes = false;
 
-        for (size_t i = 0; i < n; ++i)
+        for (size_t i = 0; i < nEnableMember; ++i)
         {
             bool bIsDate = maMembers[i].mbDate;
             bool bPartialMatch = false;
@@ -785,7 +788,7 @@ IMPL_LINK_NOARG(ScCheckListMenuControl, EdModifyHdl, weld::Entry&, void)
 
         if ( bSomeDateDeletes )
         {
-            for (size_t i = 0; i < n; ++i)
+            for (size_t i = 0; i < nEnableMember; ++i)
             {
                 if (!maMembers[i].mbDate)
                     continue;
@@ -813,7 +816,7 @@ IMPL_LINK_NOARG(ScCheckListMenuControl, EdModifyHdl, weld::Entry&, void)
         {
             std::vector<size_t> aShownIndexes;
 
-            for (size_t i = 0; i < n; ++i)
+            for (size_t i = 0; i < nEnableMember; ++i)
             {
                 assert(!maMembers[i].mbDate);
 
@@ -839,7 +842,7 @@ IMPL_LINK_NOARG(ScCheckListMenuControl, EdModifyHdl, weld::Entry&, void)
         }
     }
 
-    if ( nSelCount == n )
+    if ( nSelCount == nEnableMember )
         mxChkToggleAll->set_state( TRISTATE_TRUE );
     else if ( nSelCount == 0 )
         mxChkToggleAll->set_state( TRISTATE_FALSE );
@@ -874,7 +877,9 @@ void ScCheckListMenuControl::Check(const weld::TreeIter* pEntry)
     if (pEntry)
         CheckEntry(*pEntry, mpChecks->get_toggle(*pEntry) == TRISTATE_TRUE);
     size_t nNumChecked = GetCheckedEntryCount();
-    if (nNumChecked == maMembers.size())
+    size_t nEnableMember = std::count_if(maMembers.begin(), maMembers.end(),
+        [](const ScCheckListMember& rLMem) { return !rLMem.mbHiddenByOtherFilter; });
+    if (nNumChecked == nEnableMember)
         // all members visible
         mxChkToggleAll->set_state(TRISTATE_TRUE);
     else if (nNumChecked == 0)
@@ -938,7 +943,7 @@ void ScCheckListMenuControl::setMemberSize(size_t n)
     maMembers.reserve(n);
 }
 
-void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal, bool bVisible)
+void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal, bool bVisible, bool bHiddenByOtherFilter)
 {
     SvNumberFormatter* pFormatter = mrViewData.GetDocument().GetFormatTable();
 
@@ -972,12 +977,14 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal,
         mpChecks->insert(nullptr, -1, nullptr, nullptr, nullptr, nullptr, false, xYearEntry.get());
         mpChecks->set_toggle(*xYearEntry, TRISTATE_FALSE);
         mpChecks->set_text(*xYearEntry, aYearName, 0);
+        mpChecks->set_sensitive(*xYearEntry, !bHiddenByOtherFilter);
         ScCheckListMember aMemYear;
         aMemYear.maName = aYearName;
         aMemYear.maRealName = rsName;
         aMemYear.mbDate = true;
         aMemYear.mbLeaf = false;
         aMemYear.mbVisible = bVisible;
+        aMemYear.mbHiddenByOtherFilter = bHiddenByOtherFilter;
         aMemYear.mxParent.reset();
         aMemYear.meDatePartType = ScCheckListMember::YEAR;
         maMembers.emplace_back(std::move(aMemYear));
@@ -990,12 +997,14 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal,
         mpChecks->insert(xYearEntry.get(), -1, nullptr, nullptr, nullptr, nullptr, false, xMonthEntry.get());
         mpChecks->set_toggle(*xMonthEntry, TRISTATE_FALSE);
         mpChecks->set_text(*xMonthEntry, aMonthName, 0);
+        mpChecks->set_sensitive(*xMonthEntry, !bHiddenByOtherFilter);
         ScCheckListMember aMemMonth;
         aMemMonth.maName = aMonthName;
         aMemMonth.maRealName = rsName;
         aMemMonth.mbDate = true;
         aMemMonth.mbLeaf = false;
         aMemMonth.mbVisible = bVisible;
+        aMemMonth.mbHiddenByOtherFilter = bHiddenByOtherFilter;
         aMemMonth.mxParent = std::move(xYearEntry);
         aMemMonth.meDatePartType = ScCheckListMember::MONTH;
         maMembers.emplace_back(std::move(aMemMonth));
@@ -1009,6 +1018,7 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal,
         mpChecks->insert(xMonthEntry.get(), -1, nullptr, nullptr, nullptr, nullptr, false, xDayEntry.get());
         mpChecks->set_toggle(*xDayEntry, TRISTATE_FALSE);
         mpChecks->set_text(*xDayEntry, aDayName, 0);
+        mpChecks->set_sensitive(*xDayEntry, !bHiddenByOtherFilter);
         ScCheckListMember aMemDay;
         aMemDay.maName = aDayName;
         aMemDay.maRealName = rsName;
@@ -1018,6 +1028,7 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal,
         aMemDay.mbDate = true;
         aMemDay.mbLeaf = true;
         aMemDay.mbVisible = bVisible;
+        aMemDay.mbHiddenByOtherFilter = bHiddenByOtherFilter;
         aMemDay.mxParent = std::move(xMonthEntry);
         aMemDay.meDatePartType = ScCheckListMember::DAY;
         maMembers.emplace_back(std::move(aMemDay));
@@ -1026,7 +1037,7 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal,
     mpChecks->thaw();
 }
 
-void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal, bool bVisible, bool bValue)
+void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal, bool bVisible, bool bHiddenByOtherFilter, bool bValue)
 {
     ScCheckListMember aMember;
     // tdf#46062 - indicate hidden whitespaces using quotes
@@ -1037,6 +1048,7 @@ void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal,
     aMember.mbLeaf = true;
     aMember.mbValue = bValue;
     aMember.mbVisible = bVisible;
+    aMember.mbHiddenByOtherFilter = bHiddenByOtherFilter;
     aMember.mxParent.reset();
     maMembers.emplace_back(std::move(aMember));
 }
@@ -1255,7 +1267,7 @@ IMPL_LINK(ScCheckListMenuControl, KeyInputHdl, const KeyEvent&, rKEvt, bool)
     {
         std::unique_ptr<weld::TreeIter> xEntry = mpChecks->make_iterator();
         bool bEntry = mpChecks->get_cursor(xEntry.get());
-        if (bEntry)
+        if (bEntry && mpChecks->get_sensitive(*xEntry, 0))
         {
             bool bOldCheck = mpChecks->get_toggle(*xEntry) == TRISTATE_TRUE;
             CheckEntry(*xEntry, !bOldCheck);
@@ -1272,6 +1284,8 @@ IMPL_LINK(ScCheckListMenuControl, KeyInputHdl, const KeyEvent&, rKEvt, bool)
 size_t ScCheckListMenuControl::initMembers(int nMaxMemberWidth)
 {
     size_t n = maMembers.size();
+    size_t nEnableMember = std::count_if(maMembers.begin(), maMembers.end(),
+        [](const ScCheckListMember& rLMem) { return !rLMem.mbHiddenByOtherFilter; });
     size_t nVisMemCount = 0;
 
     if (nMaxMemberWidth == -1)
@@ -1326,7 +1340,7 @@ size_t ScCheckListMenuControl::initMembers(int nMaxMemberWidth)
             mpChecks->expand_row(*rRow);
     }
 
-    if (nVisMemCount == n)
+    if (nVisMemCount == nEnableMember)
     {
         // all members visible
         mxChkToggleAll->set_state(TRISTATE_TRUE);
@@ -1389,7 +1403,7 @@ void ScCheckListMenuControl::getResult(ResultType& rResult)
             bool bState = vCheckeds.find(aLabel.makeStringAndClear()) != vCheckeds.end();
 
             ResultEntry aResultEntry;
-            aResultEntry.bValid = bState;
+            aResultEntry.bValid = bState && !maMembers[i].mbHiddenByOtherFilter;
             aResultEntry.aName = maMembers[i].maRealName;
             aResultEntry.nValue = maMembers[i].mnValue;
             aResultEntry.bDate = maMembers[i].mbDate;
diff --git a/sc/source/ui/inc/checklistmenu.hxx b/sc/source/ui/inc/checklistmenu.hxx
index 5585bc3e846f..b400a40da2ae 100644
--- a/sc/source/ui/inc/checklistmenu.hxx
+++ b/sc/source/ui/inc/checklistmenu.hxx
@@ -36,6 +36,7 @@ struct ScCheckListMember
     OUString                 maRealName;
     double                   mnValue; // number value of filter condition
     bool                     mbVisible;
+    bool                     mbHiddenByOtherFilter;
     bool                     mbDate;
     bool                     mbLeaf;
     bool                     mbValue; // true if the filter condition is value
@@ -134,8 +135,8 @@ public:
     void queueLaunchSubMenu(size_t nPos, ScListSubMenuControl* pMenu);
 
     void setMemberSize(size_t n);
-    void addDateMember(const OUString& rName, double nVal, bool bVisible);
-    void addMember(const OUString& rName, const double nVal, bool bVisible,
+    void addDateMember(const OUString& rName, double nVal, bool bVisible, bool bHiddenByOtherFilter);
+    void addMember(const OUString& rName, const double nVal, bool bVisible, bool bHiddenByOtherFilter,
                    bool bValue = false);
     size_t initMembers(int nMaxMemberWidth = -1);
     void setConfig(const Config& rConfig);
diff --git a/sc/source/ui/view/gridwin.cxx b/sc/source/ui/view/gridwin.cxx
index c453f666acb6..6bc42b7c77a8 100644
--- a/sc/source/ui/view/gridwin.cxx
+++ b/sc/source/ui/view/gridwin.cxx
@@ -949,7 +949,7 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow)
                 bSelected = aSelectedString.count(aStringVal) > 0;
             else if (bQueryByNonEmpty)
                 bSelected = false;
-            mpAutoFilterPopup->addMember(aStringVal, aDoubleVal, bSelected);
+            mpAutoFilterPopup->addMember(aStringVal, aDoubleVal, bSelected, it->IsHiddenByFilter());
             aFilterEntries.maStrData.erase(it);
             break;
         }
@@ -959,7 +959,7 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow)
         const OUString& aStringVal = rEntry.GetString();
         const double aDoubleVal = rEntry.GetValue();
         const double aRDoubleVal = rEntry.GetRoundedValue();
-        bool bSelected = true;
+        bool bSelected = !rEntry.IsHiddenByFilter();
 
         if (!aSelectedValue.empty() || !aSelectedString.empty())
         {
@@ -970,9 +970,10 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow)
         }
 
         if ( rEntry.IsDate() )
-            mpAutoFilterPopup->addDateMember( aStringVal, rEntry.GetValue(), bSelected );
+            mpAutoFilterPopup->addDateMember( aStringVal, rEntry.GetValue(), bSelected, rEntry.IsHiddenByFilter());
         else
-            mpAutoFilterPopup->addMember( aStringVal, aRDoubleVal, bSelected, rEntry.GetStringType() == ScTypedStrData::Value );
+            mpAutoFilterPopup->addMember( aStringVal, aRDoubleVal, bSelected, rEntry.IsHiddenByFilter(),
+                rEntry.GetStringType() == ScTypedStrData::Value );
     }
 
     // Populate the menu.
diff --git a/sc/source/ui/view/gridwin2.cxx b/sc/source/ui/view/gridwin2.cxx
index 4c9e62ac3bc4..f8e83dc61864 100644
--- a/sc/source/ui/view/gridwin2.cxx
+++ b/sc/source/ui/view/gridwin2.cxx
@@ -487,9 +487,9 @@ void ScGridWindow::DPLaunchFieldPopupMenu(const Point& rScrPos, const Size& rScr
             OUString aName = rMem.getDisplayName();
             if (aName.isEmpty())
                 // Use special string for an empty name.
-                mpDPFieldPopup->addMember(ScResId(STR_EMPTYDATA), 0.0, rMem.mbVisible);
+                mpDPFieldPopup->addMember(ScResId(STR_EMPTYDATA), 0.0, rMem.mbVisible, false);
             else
-                mpDPFieldPopup->addMember(rMem.getDisplayName(), 0.0, rMem.mbVisible);
+                mpDPFieldPopup->addMember(rMem.getDisplayName(), 0.0, rMem.mbVisible, false);
         }
     }
 
diff --git a/sc/uiconfig/scalc/ui/filterdropdown.ui b/sc/uiconfig/scalc/ui/filterdropdown.ui
index 8e0c01748e33..726cab40de94 100644
--- a/sc/uiconfig/scalc/ui/filterdropdown.ui
+++ b/sc/uiconfig/scalc/ui/filterdropdown.ui
@@ -54,6 +54,10 @@
       <column type="gboolean"/>
       <!-- column-name checktri1 -->
       <column type="gboolean"/>
+      <!-- column-name weight1 -->
+      <column type="gint"/>
+      <!-- column-name sensitive1 -->
+      <column type="gboolean"/>
     </columns>
   </object>
   <object class="GtkTreeStore" id="treestore2">
@@ -68,6 +72,10 @@
       <column type="gboolean"/>
       <!-- column-name checktri1 -->
       <column type="gboolean"/>
+      <!-- column-name weight1 -->
+      <column type="gint"/>
+      <!-- column-name sensitive1 -->
+      <column type="gboolean"/>
     </columns>
   </object>
   <object class="GtkPopover" id="FilterDropDown">
@@ -265,6 +273,7 @@
                                 <child>
                                   <object class="GtkCellRendererToggle" id="cellrenderer5"/>
                                   <attributes>
+                                    <attribute name="sensitive">6</attribute>
                                     <attribute name="visible">3</attribute>
                                     <attribute name="active">0</attribute>
                                   </attributes>
@@ -272,6 +281,7 @@
                                 <child>
                                   <object class="GtkCellRendererText" id="cellrenderer4"/>
                                   <attributes>
+                                    <attribute name="sensitive">6</attribute>
                                     <attribute name="text">1</attribute>
                                   </attributes>
                                 </child>
@@ -315,6 +325,7 @@
                                 <child>
                                   <object class="GtkCellRendererToggle" id="cellrenderer1"/>
                                   <attributes>
+                                    <attribute name="sensitive">6</attribute>
                                     <attribute name="visible">3</attribute>
                                     <attribute name="active">0</attribute>
                                   </attributes>
@@ -322,6 +333,7 @@
                                 <child>
                                   <object class="GtkCellRendererText" id="cellrenderer2"/>
                                   <attributes>
+                                    <attribute name="sensitive">6</attribute>
                                     <attribute name="text">1</attribute>
                                   </attributes>
                                 </child>