Connect Slicer to Multiple Data Sources

[Mahonei]

Hello All,

I am trying to have a Slicer filter data in multile pivot charts. This has worked for me in the past, but all the pivot charts where built off of the same source table. In this situation... I have one table with data structured by Cost Accounting elements, and another table with Data structured by Financial Accounting elements. Both tables have the same "Location" field in them. I want to be able to make a Location slicer that filters the data in both pivot tables/charts.

I've attached a dumbed down version of what I am trying to do. If anyone can get that slicer to work on both those charts... I'd be very appreciative.

Much Thanks,
Mahonei

 

[Luke M]

Sadly, it appears this can't be done. You might be able to write a macro that forcibly changes all slicers, but I'm not sure.
http://stackoverflow.com/questions/...pivot-tables-with-different-data-source-in-ex

 

[Mahonei]

Ya, I was trying to go the VBA route, but it wasn't working for me. What I ended up doing was assigning each transaction a unique identifier and then using allot of IF and VLOOKUP to build a table with all the data. From there you can make the slicers work. Here is a working example incase anyone is interested. It doesn't contain the formulas though, since I don't have time to write up ones for the example right now. But you can get the idea.

Here is and example of what I used in my actual file for work:

=IF(LEFT([@[Corporate Key]],1)="P",VLOOKUP([@[Corporate Key]],Table2[[PABR Key]:[Funds Center]],6,FALSE),IF(LEFT([@[Corporate Key]],1)="F",VLOOKUP([@[Corporate Key]],Table4[[Fstatus Key]:[Fund Center]],4,FALSE),VLOOKUP([@[Corporate Key]],Table6[[Trial Balance Key]:[Account]],3,FALSE)))