I am using power query to get my tables from an external workbook. When I create a slicer for one of my pivot tables the other pivot tables don't show up in the pivottable connection. I want to have one slicer for multiple pivot tables and I am not sure why the pivot tables isn't showing up. Any help would be awesome!
0
votes
As teylyn points out, PivotTables need to be on the same PivotCache in order to do this out of the box. That said, you can also use VBA in need to do this. Are you wanting to sync the PivotTables on just one item? Or multiple items?
– jeffreyweir
I want to force the user to only be able to select one item in the pivot table and if they click that i want to take effect on multiple pivot tables.
– User
I don't have power pivot so VBA would be my only option.
– User
2 Answers
1
votes
A slicer can only connect to a pivot table that uses the same pivot cache. Chances are that if you don't see the other pivot tables in the slicer connection, they are using a different pivot cache.
If you use Power Query to get the data, make sure that you add the query to the workbook data model. Pivot tables that are created from the data model will share the same pivot cache.
0
votes
Given you don't have PowerPivot installed (or don't have the DataModel that is built in to Excel 2013 or later) then you've got two options:
Join both data sources in PowerQuery on a common field, so that you bring in just one Table. Then everything will be on the same PivotCache (allowing Slicers) if not PivotTable.
Use one VBA routine to limit slicer selection to one item only, and another routine to sync slicers across different PivotCaches.
If you elect for the latter, then Jerry Sullivan's code from this link will limit users to selecting just one item in a Slicer, and my code from https://stackoverflow.com/a/39604425/2507160 will then show you how to sync this Slicer to other Slicers on different PivotCaches, in order to keep all Pivots showing the same thing.
