Slicers to sync multiple pivots from multiple data sources?

Hi all,
Help please? Firstly what I want to do is to a a slicer control all pivot tables irrespective of which data connection they have. For instance I have workbook using several different external connections, but every single one has 'name' as a field with the same list of names.

I have read alot of post today and come to the conclusion that VB is required, but not found a solution that yet makes it work. So basically I have a filter page with multiple slicers, which when changed would ripple through to all slicers and ultimately Pivottables. Ideally it would be to any worksheet and any pivot table. Also any common field could be amended, and could be a single item selected or multiple items.

The data sources are generally sql queries against various DBs.

Any feedback, help, examples and advice would be much appreciated.


Thanks

-CL

Hi CL,

Check the attached file; there are 4 Pivot Tables, 4 Charts & 4 Slicers which are linked with each other.
If you click on any Slicer; all of the charts will be updated automatically.

If this does not resolve your problem then post your sample file with atleast 2 PT & Slicers.

Regards,
AM

This approach doesn't work- as it is all on a single data source. User above has multiple datasources but wants to sync slicers across it.

I just had it working but had an Excel crash. Will try to re-build and share.

Were you able to get it to work? I have the exact same question as you.

I have been searching for a good answer for this also, I have 4 Pivot tables each with its own data source. Slicers for each Pivot table with mostly the same items.
I want to sync One slicer across all slicers, so what ever slicer item I select on the main one will select on the rest of the slicers

I have the same question as well. Trying to work with a single slicer on multiple data sets with common column names.

Hi Guys,

After one day googling and tearing my hair because of failure I found a solution to this !!!!!

So when you have different data sources (lets say 2) you need to create a third one which you use as primary key to connect both data sources. The primary key must be just one (no duplicates) so we can create one to many connection.

All sources must be connected twice in Analyze Tab Relationships and in slicer options - report connection. And another important thing is to tick box "Add data to Data model " when creating Pivot table.

So how to do it :

We have 2 data source where we wanna filter one column, lets say Region. We create new table for primary keys with column A Region ID (or whatever) and column B Region.

From all 3 tables we create Pivot tables where we MUST tick option "Add data to Data model"

Then we need to create relationships between tables. So when u click to pivot table in Analyze tab choose Relationships and click new. (if it created automatically delete it and do it manually). Now you need to create 2 connections (one connection for each data source) with primary table. So primary key table / column Region to source 1 / column region and primary key table / column Region to source 2 / column region.

Now we create Slicer from Primary key Pivot table, right click and choose report connections and tick both pivot tables u want to create connection to.

And voila.

What the above post is pointing persons' toward is data modeling. Google/research that for Excel best practices. Referential Integrity is a must.