Sep 25 2020 09:05 AM
I am looking for an Excel formula that will return the list of Visible PivotItems in a specific PivotField. Any ideas?
Sep 25 2020 11:33 AM
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)
Sep 25 2020 01:42 PM
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