Multiple drop down lists, dependent on previous lists data

[dngsullivan]

Hi,

I need some help creating drop down lists that narrow down as previous criteria is selected.

I have a sheet containing data (that is constantly added to) with the following columns

Supplier Product Colour Size Price


I would like to have a drop down list containing all suppliers from the above, then the 2nd drop down list would contain all products from that supplier, the 3rd drop down would have colours available in that product for that supplier (& so on).

I've watched so many different videos and read so many websites, but none of the suggestions seem to work for me.
Please help!

 

[Misca]

The easiest solution to your problem would be using Pivot Tables with slicers. This way you could use your drop downs any way you like to and the rest of the slicers would always update as long as you remember to refresh the cache whenever your data changes.

If you're using Excel 2013 or later you can use slicers with data tables as well.

 

[dngsullivan]

That's awesome! Thanks Misca, I had never heard of slicers before.

It does do what I want, however its quite a bit bigger than I had planned (I have 14 categories to drill down from - I simplified it to 4 categories in my original post).

Do you know how to create a macro to clear all the filters? I have tried the 2 following ones, but both don't appear to do anything.

 Sub ClearMySlicers()
Dim Slcr As SlicerCache
For Each Slcr In ActiveWorkbook.SlicerCaches
    Slcr.ClearManualFilter
Next
End Sub

and

 Sub SlicerReset()

    'Resets all Slicers at once
    Application.ScreenUpdating = False
    
    Dim Cache As SlicerCache
     
    For Each Cache In ActiveWorkbook.SlicerCaches
        Cache.ClearManualFilter
    Next Cache
    
    Application.ScreenUpdating = True

End Sub 

 

[Misca]

I would've used the first one but if that doesn't work, try

With ActiveSheet.PivotTables(1)    
.ClearAllFilters
End With

 

[dngsullivan]

Hi Misca,

I don't know what I'm doing wrong. All 3 don't seem to work.
I am very new to VBA & slicers.

I've tried adding all 3 codes (at separate times) and assigning the macro to a button.
Is there a better way - as all it does is sends me to another sheet in my workbook and all slicers are still filtered.

Thanks

 

[Misca]

The last piece of code (starting with "with ActiveSheet") needs to be in a sub:

Sub RemoveFilters()

With ActiveSheet.PivotTables(1)    
	.ClearAllFilters
End With


End Sub

The macro button needs to be on the same sheet where the pivot table is ("ActiveSheet" = the sheet that is active / selected when this part of the macro is being run).

Also, the ActiveSheet.PivotTables(1) means that it only affects the first pivot table on that sheet. If there's more than one, you might want to change the macro to loop through all the pivot tables on that sheet (or maybe all the pivot tables in your workbook?).

Other than that I can't think of anything else where the problem could be: When I'm running the codes with my (simple) pivot tables, they seem to work just fine. If you're still getting errors you might want to upload your workbook someplace where I could download it and try to find out what the problem might be.

If the data on your spreadsheet is confidential replace the real data with some dummy data (but keep the data types & column names as they are).