Add Criteria Settings
In the All column, for each field that has a Slicer, type the criteria setting that you want to use if the Slicer is cleared, and the pivot table filter shows "(All)".
For the text fields, I entered an asterisk, and for the number fields I used ">0". You could use different criteria, based on your data.
Get the "All" Criteria
Below the Criteria range, another set of formulas will get the criteria setting from our table, for cases when "All" is selected. The formula uses the INDEX and MATCH functions to pull the values from the Field List table.
Enter the following formula in cell D7, and copy it across to F7
=INDEX(tblHead[[All]:[All]],MATCH(D3,HeadingsList,0))
The formula looks for the field name in cell D3, and finds its match in the HeadingsList range. Then, it returns the value from the All column in that row.
Add the Criteria Formulas
Next, we'll add formulas in the criteria row, to use with the Advanced Filter. Each formula is slightly different, and refers to its field name and value in the pivot table filters. The formula checks the field in the source data, to see if the selected item appears.
- If the item is found in the source data, that item is used as the criterion.
- If the item isn't found, e.g. (All) or (Multiple Items), the alternate criterion is used.
Enter the following formula in cell D4:
=IF(COUNTIF(INDIRECT("Sales_Data[" & A3 & "]"),B3),B3,D7)
Enter the following formula in cell E4:
=IF(COUNTIF(INDIRECT("Sales_Data[" & A4 & "]"),B4),B4,E7)
Enter the following formula in cell F4:
=IF(COUNTIF(INDIRECT("Sales_Data[" & A5 & "]"),B5),B5,F7)
Here's the result, if none of the Slicers are cleared. I've used grey fill on all the cells with formulas. The Advanced Filter would return all the records in the Sales_Data table
Here's the result, after selections are made in the Category and Orderyr Slicers. The Advanced Filter would return all the records for Snacks sales in 2016, for any Region.
Add the Macro Code
Next, you'll add the macro -- GetDataForSlicersSel -- that runs an Advanced Filter. Add the following code to a regular module in the workbook:
