Connect slicer to pivots from different data sources?

[MikeLiberty]

I have 2 data tables on a worksheet with similar dimensions, both have columns for Market, Business Group, Dept etc... I can't combine them into one data set because they are at different levels of aggregation below the department.

I have created a pivot table from each data set and put them on the same sheet. I want to have a single slicer for Market, that will control both pivot tables. I've been able to connect a slicer to two pivot tables from the same data set, but the pivot table based on the other data set never shows up in my list of available slicer connections.

Is it possible?

 

[RoryA]

To the best of my knowledge you can only share slicers between tables built off the same data source.

 

[MikeLiberty]

Not what I wanted to hear. Thanks for the reply.

Do you (or anyone) know if its possible to use an on-click type macro with a slicer? So i could have one slicer be selected and update a second slicer with the same selections?

 

[RoryA]

I don't think so. If you assign a macro to the slicer, then you can't actually use it as a slicer, and there aren't any events for slicers. The closest thing you could do would be to use the worksheet_pivottableupdate event to keep the tables in sync. Sadly, that can be a fair bit of work, although if you are using OLAP at least you have the visibleitemslist to work with.

 

[MikeLiberty]

I ended up connecting the pivot tables using a for each loop to walk through the filter items and set them equal. Unfortunately, this takes forever to execute in long lists and I have to do a lot of error handling if the lists don't match precisely (one contains blanks for example).

Does anyone know a better way to set the filter in one pivot table equal to the selections made in another pivot table? I'm hoping there is an array or something i can use.

As an example. I have a list of 1000 offices that appears in 2 different pivot tables based on 2 different data sources. the offices are the same in both lists but I can't combine the data because of the levels of aggregation i need to do.

Is there a way to set the selected offices on the second pivot table equal to the selections made on the first? Without going through each office and checking if it's visible = true?