Today I was looking at an old post from www.powerpivotpro.com https://powerpivotpro.com/2011/10/user-friendly-report-sorting-with-slicers/
Rob Collie uses an ingenious technique to sort the Pivot Table Report using Slicers. Of Course this is done using DAX.
Most of the Excel Users would not always be working on Power Pivot and DAX.
BUT we can borrow this idea and use some VBA magic to sort the our Normal Tables in Excel using Slicers
Click here to Download this file
You can look at the VBA code in the Workbook to see how it works. Nevertheless, I will write down the key point here
- Just like Rob Collie, I created 2 dummy tables “Sort Order” and “Sort By”
- Then I created two pivot tables based on these Excel Tables. WHY? Because I need Pivot table Update Event to run the macro
- Next I created slicers from each of these pivot tables
- Now I have to write a macro that would output the slicer item selected in Excel. In VBA we can use VisibleSlicerItems.count to check if only one item is selected. If there is only 1 item selected, we can VisibleSlicerItems(1).value to output that value in an Excel Cell
REST should be easy peasy for you to understand after you explore the Workbook you downloaded above