Visible Pivit Items in a Pivot Field

Copper Contributor

I am looking for an Excel formula that will return the list of Visible PivotItems in a specific PivotField. Any ideas?

2 Replies

@Mgaill 

Here is a custom VBA function:

Function ListPvtItems(r As Range, Optional sep As String = ", ") As String
    Dim pvf As PivotField
    Dim s As String
    Dim pvi As PivotItem
    Set pvf = r.PivotField
    For Each pvi In pvf.PivotItems
        If pvi.Visible Then
            s = s & sep & pvi.Name
        End If
    Next pvi
    If s <> "" Then
        ListPvtItems = Mid(s, Len(sep) + 1)
    End If
End Function

Use like this in a cell formula:

 

=ListPvtItems(A3)

 

where A3 is a cell in the pivot field you want to list the visible items of. Or

 

=ListPvtItems(A3,CHAR(10))

 

to list the items on separate lines (turn on Wrap Text for the cell with the formula)

@Hans Vogelaar 

Thanks a lot, it is working like this (without the "enf if"

Function ListPvtItems(r As Range, Optional sep As String = ", ") As String
' Custom VBA function to list Pivot list item in a Pivot Field:
Dim pvf As PivotField
Dim s As String
Dim pvi As PivotItem
Set pvf = r.PivotField
For Each pvi In pvf.PivotItems
If pvi.Visible Then s = s & sep & pvi.Name
Next pvi
If s <> "" Then ListPvtItems = Mid(s, Len(sep) + 1)
End Function