Forum Discussion

Reception305's avatar
Reception305
Copper Contributor
Oct 24, 2024
Solved

Drop Down List Filter Question

So I used a filter function formula to filter data based on specific category or vendor name like so: However, what I realized is that what this filters is readable data not editable data. If ...
developer
excel
Formulas and Functions
Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    Oct 29, 2024

    Hi (politeness obliges me ) Reception305 

    Why not putting in place https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d on your "Master" Table, instead of drop-down lists?

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 02, 2024

Reception305 

A possible way to go is attached. There's a setup_sheet to set the rules & TableSlicer with a couple of calcs. to determine what slicer has been used first + hidden columns on the main sheet

 

As you probably already figured out there are necessarily some conflicts, i.e: if you say dates should be hidden for Beverages (has Vendors: Costo, Amazon & Safeway) and you say dates should not be hidden for Costco (???). As you have more Categories than Vendors I assumed you would use the Category slicer first and implemented the corresponding logic in TableSlicer

 

It's not 100% perfect though. If you filter by 2 slicers you may get the following:

something I couldn't resolve

 

BTW, in almost all formulas I had to TRIM as you have leading spaces here & there

Reception305's avatar
Reception305
Copper Contributor
Nov 04, 2024
Lorenzo yeeea I had a feeling it would be something like that. Although, it's not 100% it does get the job done and solves the issue I was facing. Thank you for all your help!!!!!! The formulas you provided in the file were great!!!!
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 04, 2024

    Reception305 

    I wish I could make it 100%... Glad it helped anyway

Resources