CODEWITHSUNDEEP
excelexcel-formulapivot-tablevba
NB_Excel
NB_Excel

Reputation: 115

Showing selected slicer item in a cell

Any ide how to show selected slicer item in a cell?

I have tried to use this UDF code:

Public Function SlicerSelections(Slicer_To_Project_Name1 As String) FblSlicerSelections = "" Dim i As Integer With ActiveWorkbook.SlicerCaches(Slicer_To_Project_Name1) For i = 1 To .SlicerItems.Count If .SlicerItems(i).Selected Then SlicerSelections = SlicerSelections & " " & .SlicerItems(i).Value End If Next i End With End Function

But I dont get any response?

Upvotes: 2

Views: 22512

Answers (1)

jeffreyweir
jeffreyweir

Reputation: 4824

Here's a simpler solution: make a copy of the PivotTable, and remove all fields from the copy other than the one that the Slicer controls. Make that remaining field a PageField, and then connect that new PivotTable to the Slicer. You'll see that this takes up just two cells. Then any time anyone clicks the Slicer, that two-cell PivotTable will be filtered on that selection automatically. Granted, this takes up two cells worth of space instead of one. But there's no VBA code necessary.

The concept is similar to what I talk about in the following blogpost: http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

Upvotes: 2

Related Questions