PivotTable's Report Filter using "greater than"
excel, pivot-table
Solution
After some research I finally got a VBA code to show the filter value in another cell:
Dim bRepresentAsRange As Boolean, bRangeBroken As Boolean
Dim sSelection As String
Dim tbl As Variant
bRepresentAsRange = False
bRangeBroker = False
With Worksheets("Forecast").PivotTables("ForecastbyDivision")
ReDim tbl(.PageFields("Probability").PivotItems.Count)
For Each fld In .PivotFields("Probability").PivotItems
If fld.Visible Then
tbl(n) = fld.Name
sSelection = sSelection & fld.Name & ","
n = n + 1
bRepresentAsRange = True
Else
If bRepresentAsRange Then
bRepresentAsRange = False
bRangeBroken = True
End If
End If
Next fld
If Not bRangeBroken Then
Worksheets("Forecast").Range("ProbSelection") = " >= " & tbl(0)
Else
Worksheets("Forecast").Range("ProbSelection") = Left(sSelection, Len(sSelection) - 1)
End If
End With
Problem
I have a pivot table which has one of the fields (Probability) in a Report Filter. Its values are percentages in step of 5 (0,5,10,15,...,100). I'd like to use it to filter probabilities greater than or equal a certain value, but the filter only filters exact choices. For now I use a workaround of allowing multiple values, and then selecting all values from the threshold I want, all the way to 100 This solution, apart from being awkward, doesn't show my selection, which is necessary as this table is being printed out.The Filter's display value is "(Multiple Values)" and I'd like to show all the values selected, or even better, something like ">=20%". I don't really care if they show in the field itself or in another cell outside the Pivot table. My questions: 1) can I get the filter to filter on >= of my seletion? If not 2) Can I show the multiple selections like ">=20%"