extract data value from Pivot filter (Multiple Items)

[yessuz]

Hi Guys,

i wonder if there is any way to extract the value of the filter on the pivot table when you select more than 1 criteria?

For example:
If my filter criteria has "apples", "Pears", "oranges" and I select "Apples" and "pears" in the filter criteria, the value of the filter is shown as (Multiple Items)
If I select "Oranges" - the value of filter is shown as Oranges.

then I can use the formula to get the cell value of the cell.

But when the multiple items is selected, the value is just (Multiple Items).

Is there any way to extract the value of filter criteria in order to get Apples and Oranges as the result?

thanks!!!

 

[John_w]

Try this UDF:

Public Function Get_PT_Field_Items(PivotTableName As String, PivotField As String) As String

    Dim PT As PivotTable
    Dim PTfield As PivotField
    Dim PTitem As PivotItem
    
    Set PT = ActiveSheet.PivotTables(PivotTableName)
    Set PTfield = PT.PivotFields(PivotField)
    
    Get_PT_Field_Items = ""
    For Each PTitem In PTfield.PivotItems
        Debug.Print PTitem.Name
        If PTitem.Visible Then Get_PT_Field_Items = Get_PT_Field_Items & PTitem.Name & ", "
    Next
    
    If Get_PT_Field_Items <> "" Then
        Get_PT_Field_Items = Left(Get_PT_Field_Items, Len(Get_PT_Field_Items) - 2)
    End If
    
End Function

Use it like this in a cell formula:

=Get_PT_Field_Items("PivotTable1","Fruit")

where "PivotTable1" is the name of the pivot table and "Fruit" is the name of the field.

However, the above cell formula is not recalculated when you change the filter field items. Instead you have to call it from the Worksheet_PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Range("H1").Value = Get_PT_Field_Items(Target.Name, "Fruit")
End Sub

where H1 is the cell you want to contain the list of selected items.

 

[LocalAcceleration]

Try this UDF:

Public Function Get_PT_Field_Items(PivotTableName As String, PivotField As String) As String

    Dim PT As PivotTable
    Dim PTfield As PivotField
    Dim PTitem As PivotItem
   
    Set PT = ActiveSheet.PivotTables(PivotTableName)
    Set PTfield = PT.PivotFields(PivotField)
   
    Get_PT_Field_Items = ""
    For Each PTitem In PTfield.PivotItems
        Debug.Print PTitem.Name
        If PTitem.Visible Then Get_PT_Field_Items = Get_PT_Field_Items & PTitem.Name & ", "
    Next
   
    If Get_PT_Field_Items <> "" Then
        Get_PT_Field_Items = Left(Get_PT_Field_Items, Len(Get_PT_Field_Items) - 2)
    End If
   
End Function

Use it like this in a cell formula:

=Get_PT_Field_Items("PivotTable1","Fruit")

where "PivotTable1" is the name of the pivot table and "Fruit" is the name of the field.

However, the above cell formula is not recalculated when you change the filter field items. Instead you have to call it from the Worksheet_PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Range("H1").Value = Get_PT_Field_Items(Target.Name, "Fruit")
End Sub

where H1 is the cell you want to contain the list of selected items.

Hey this is great, I'm struggling a little with this code though. Every time I go in and change the pivot table it doesn't seem to change anything. Any help would be huge! thank you!

 

[LocalAcceleration]

Hey this is great, I'm struggling a little with this code though. Every time I go in and change the pivot table it doesn't seem to change anything. Any help would be huge! thank you!

I realized I messed up the code here. I have updated it and it's now showing G,S,X,Y in the cell I have selected. However I am only currently filtering by X and Y in the pivot table filter that the code is pulling from. Is there a way to only have it show the selected items?

 

[John_w]

showing G,S,X,Y in the cell I have selected. However I am only currently filtering by X and Y in the pivot table filter that the code is pulling from. Is there a way to only have it show the selected items?

I'm unable to reproduce that problem. Could you upload the workbook to a file sharing site and post the link here and I'll try to get it working for you.

 

[Alex Blakenburg]

If you are happy to use slicers and a connected pivot table to give a list of the filter items you can use the techniques described here:

3 Ways to Display (Multiple Items) Filter Criteria in a Pivot Table - Excel Campus

Learn how to create a list of the (Multiple Items) that are filtered for in the pivot table Fields area.

www.excelcampus.com