Over at the Excel Guru forum, Yewee asks:
I have 3 sheets in my excel worksheet.
1. Org
2. DataSource
3. Pivots Table
My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.
How can I do that?
Incredibly easily, if you have Excel 2010 or later…because:
- a PivotTable with nothing but one field in the Filters pane looks and behaves pretty much exactly like a Data Validation dropdown does; and
- that PivotTable can be hooked up to the other PivotTables via slicers, so that it controls them.
If you’re a long-time reader of this blog you probably already know that, and may want to skip to the end to find a bit of VBA that makes setting up Slicers slightly more easy. But if you came here via Google, then pull up a pew and read on.
So let’s say these are the two Pivots that you want to control via a dropdown, and you want to put the dropdown where the red rectangle is:
First, create a new PivotTable from the datasource that the other pivots share (or make a copy of one of the existing Pivots) and in the PivotTable Fields pane add the field you want to filter the other Pivots by to the Filters pane. (If you created this Pivot by copying another, remove any other fields that might appear).
Great: Now you have a PivotTable masquerading as a Data Validation Dropdown. From now on, I’ll call it the ‘Master Pivot’. So just drag that Master Pivot where you want it:
From the ANALYZE tab of the PivotTable Tools contextual menu in the ribbon, click the Insert Slicer icon:
…and from the menu that comes up, choose the field name that matches the field you put in the Master Pivot:
…and your slicer will magically appear:
Now we connect that Slicer to the other PivotTables. To do that, right click on the Slicer that just appeared, and click the Report Connections option:
You’ll see from the Report Connections box that comes up that currently it’s only connected to one PivotTable – which of course is the Master PivotTable that we used to insert the slicer in the first place:
What we want to do is connect it to the other PivotTables, by checking those other checkboxes:
(Optional) We might want to make it so that the user can only select one thing at a time by clicking on the Master Pivot filter dropdown, and unchecking Select Multiple Items, if that’s your intent:
…and now all we need to do is move that Slicer somewhere out of sight (but don’t delete it):
Now when we select a region from that Master Pivot dropdown…
… all the other Pivots are filtered to match:
That’s it…job done. As simple as possible, and no simpler.
Actually that’s a lie…unless there’s a good reason not to, it’s much simpler just to use a Slicer in the first place, and not bother with setting up the Master Pivot dropdown at all:
Of course, that Slicer takes up much more room than our Master Pivot dropdown. So maybe that’s a good reason to use the Master Pivot approach, and not a slicer. Especially if we might want more than one dropdown to control all the Pivots and space is at a premium:
Or you can do away with the Master Pivot altogether, and just set the slicers up between the actual ‘output’ pivots themselves, so that as soon as they change a PivotFilter setting in one of the Pivots, the others get changed too. (Note that this also happens with the ‘Master Pivot’ approach…it’s just that we don’t actually need to have that Master Pivot sitting there taking up space at all).
Programatically add and connect Slicers
I’ve always found it annoying that there’s no right-click option to add a Slicer to the currently selected PivotField. Plus connecting Slicers to multiple PivotTables is a drag. And also, I hate it how it adds new Slicers over the top of old slicers. So here’s some code that remedies all that:
Dim pt As PivotTable
Dim ptOther As PivotTable
Dim pf As PivotField
Dim pc As PivotCache
Dim rng As Range
Dim sc As SlicerCache
Dim varAnswer As Variant
Dim bFoundCache As Boolean
Dim rngDest As Range
Set rng = ActiveCell
On Error Resume Next ‘in case user has not selected a PivotField
Set pt = rng.PivotTable
Set pc = pt.PivotCache
Set pf = rng.PivotField
On Error GoTo 0
If pt Is Nothing Then Exit Sub
If pf.Orientation <> xlDataField Then
Set rngDest = Intersect(ActiveCell.EntireRow, ActiveCell.Offset(, ActiveCell.CurrentRegion.Columns.Count + 1))
On Error Resume Next ‘SlicerCache might already exist
With rng
If pt.PivotCache.OLAP Then
Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.CubeField.Name)
Else: Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.Name)
End If
sc.Slicers.Add SlicerDestination:=ActiveSheet, Top:=rngDest.Top, Left:=rngDest.Left
End With
If Err.Number > 0 Then ‘SlicerCache already existed. Work out what it’s index is
On Error GoTo 0
For Each sc In ActiveWorkbook.SlicerCaches
For Each ptOther In sc.PivotTables
If ptOther = pt Then
bFoundCache = True
Exit For
End If
Next ptOther
If bFoundCache Then Exit For
Next sc
End If
varAnswer = MsgBox(Prompt:="Make Slicer control the " & pf.Name & " field in all Pivots on the same sheet?", Buttons:=vbYesNo)
If varAnswer = vbYes Then
For Each ptOther In ActiveSheet.PivotTables
If ptOther.CacheIndex = pt.CacheIndex And ptOther.Parent.Name = pt.Parent.Name Then
sc.PivotTables.AddPivotTable ptOther
End If
Next
End If
Else: MsgBox "You can’t add a Slicer to a Values field."
End If
End Sub
In addition, the below code will add the Add Slicer icon to the right-click menu that comes up when you right click on a PivotField:
Private Sub Workbook_Open()
AddShortcuts
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteShortcuts
End Sub
Sub AddShortcuts()
Dim cbr As CommandBar
DeleteShortcuts
Set cbr = Application.CommandBars("PivotTable Context Menu")
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Add Slicer"
.Tag = "AddSlicer"
.OnAction = "AddSlicer"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("SlicerInsert", 16, 16)
End With
End Sub
Sub DeleteShortcuts()
Dim cbr As CommandBar
Dim ctrl As CommandBarControl
Set cbr = Application.CommandBars("PivotTable Context Menu")
For Each ctrl In cbr.Controls
Select Case ctrl.Tag
Case "AddSlicer"
ctrl.Delete
End Select
Next ctrl
End Sub
…meaning whenever I right click on a PivotField I get this:
Clicking on that adds a Slicer to the selected field automatically, plus asks you:
Hell yes, I do!
Here’s a sample file:
Sync-PivotTables-from-dropdown_20140818
