MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sync Slicers from Different Data Sets


June 22, 2017 - by Bill Jelen

Sync Slicers from Different Data Sets

Slicers are awesome for pivot tables because you can control multiple pivot tables from one set of slicers. But - that is sort of a lie. You can control multiple pivot tables that came from the same data set. When you have pivot tables that came from two different data sets, it is pretty tricky. I will show you some VBA that will let you pull this off.

Watch Video

  • How can you have a slicer drive two pivot tables?
  • If both pivot tables came from the same data set: Select Slicer, Report Connections, Choose Other Pivot Tables
  • But if the pivot tables came from different data sets:
  • Use Save As to change the workbook extension to XLSM instead of XLSX
  • Use Alt + T M S and change macro security to second setting.
  • Alt + F11 to get to VBA
  • Ctrl + R to display the project explorer
  • Find the worksheet that contains your first pivot table and slicer
  • Insert the code for Worksheet_Update
  • Hide the second slicer away so it keeps existing but no one can ever choose from that slicer

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2104 synchronized slicers from different
  • data sets hey welcome back to the mr.
  • excel net cast I'm Bill Jelen and today's
  • question is not about how to take these
  • two pivot tables that came from one data
  • set and make the slicer control all
  • those pivot tables that's not what this
  • is about
  • that's an easy thing to do slicer tools
  • options either report connections or
  • slicer connections in the old version
  • and check that you want this slicer to
  • control all of those pivot tables easy
  • right this question is about this
  • worksheet where we have two different
  • data sets and we're going to create a
  • pivot table from this and from this now
  • let me speed up the video while I create
  • these pivot tables
  • alright now what you're going to see is
  • I have to pivot tables this pivot table
  • is created from one data set and there's
  • a slicer that controls that pivot table
  • and then I have a second pivot table
  • that's created from a different data set
  • and a slicer the controls that pivot
  • table but there's absolutely no way to
  • get this slicer to control both this
  • pivot table and this pivot table that's
  • built from a different data set all
  • right but I'm going to show you how to
  • do that today with a macro now this is
  • tricky to do I'm when the question came
  • in I said ah now this I don't think you
  • can do it but I've been working on it
  • and experimenting and I think I finally
  • got it I have to think I finally got a
  • dam all right so let's let's go through
  • this first off this is safe there's an
  • XLS X file that's a fine file type
  • except for it's a horrible file type
  • because it's the only file types it
  • doesn't allow macros you have to change
  • this from xlsx to xlsm or all of your
  • work to the rest of the video is going
  • to be thrown out the window save as
  • change the file type to xlsm or heck XLS
  • be either one of those will work that's
  • the one that is broken so xlsm it's the
  • default crazy isn't it xlsm click Save
  • if you've never done macros before ulti
  • for Tom M for macro s for security and
  • you will be it to save all macros
  • without notification need to change that
  • to the second one that will allow your
  • macros to work all right now we have two
  • slicers that you never do this but
  • slicers have names we're gonna go to
  • slicer tools options slicer settings and
  • see this one is called slicer underscore
  • name like that go to the second one go
  • to slicer tools options slicer settings
  • this one's called slicer underscore name
  • 1 not namespace one name one - names
  • like that because what we're going to do
  • we're going to switch over to VBA alt
  • f11 in VBA if you've never done VBA v40
  • grams this big gray screen we're going
  • to come here and save you project
  • Explorer in the project explorer find
  • your file mine called podcast 2104 open
  • Microsoft Excel objects and the sheet
  • where I want this to work is called
  • dashboard I'm going to right click there
  • and say view code this code that we're
  • writing cannot go in a module like in a
  • regular macro this has to be on this on
  • this worksheet open the top left
  • drop-down worksheet
  • then in the top right drop-down we're
  • going to say pivot table update alright
  • so this is where our codes going to go
  • now I've already pre-baked this code
  • let's take a look at the code here in
  • notepad and so we're going to have to
  • slicer caches sc1 and sc2 one slicer
  • item and then right here this is where
  • you're gonna have to customize it so my
  • two slicers were called name and name
  • one
  • all right you're gonna have to put your
  • slicer names in their application
  • off-screen update angles false
  • application enable events equals false
  • and then slicer class slicer cache -
  • we're going to clear the filter and then
  • for each item si one in SC one slicer
  • items if it's selected then we're going
  • to make the same item in slicer cache to
  • be selected this is a little loop that
  • will run through however many items
  • happen to be in that slicer in my case I
  • have 11 or 12 in your case you might
  • have more when we're done with that turn
  • enable events back on turns screen
  • updating con alright so we'll take this
  • code copy this code and paste it here in
  • the middle of our macro like that
  • alright now let's just make sure I'm
  • gonna press ctrl-g how my ass for is
  • application dot enable events on or off
  • so question mark space application not
  • enable events and it's true if yours
  • comes up as false then you want to come
  • back up here and say that it's equal to
  • true so what you're turning those events
  • on alright now here's what's going to
  • happen so our coach should be working
  • here it's on the right worksheet we're
  • saved in an excel sm file and I turned
  • macros on and what we're going to see is
  • that when I choose from the left slicer
  • that slicer cache one I'll choose an D
  • through delle the other slicer is going
  • to update as well all right and even if
  • I would choose just Gloria just Gloria
  • it looks like it's working really really
  • well even I would ctrl click when I let
  • go control they all three will update
  • but here's the gotcha there's always a
  • gotcha
  • this slicer it has to exist but you
  • cannot use this slicer wait I mean you
  • can you can use a slicer but it's going
  • to confuse the heck out of things
  • because what's going to happen is I'm
  • going to change this to Hank and they're
  • going to go back to whatever is in
  • slicer cache one because I changed the
  • pivot table on this sheet now in real
  • life are you going to have to pivot
  • tables on the same sheet I don't know if
  • you are or if you aren't all right but
  • things are going to become a little
  • crazy now let's just take a look at this
  • first thing I want to do because I'm
  • going to insert a new worksheet all iw4
  • insert worksheet and I'm going to call
  • this a dark cave you can call it
  • whatever you want I'm going to take that
  • dashboard that's not going to work I'm
  • going to copy that dashboard and come
  • here to the dark cave and paste it there
  • and then right click and hide that sheet
  • so no one ever sees that that that
  • slicer and then from here we should be
  • able to delete it nice all right and
  • we're going to just check to make sure
  • they're still working to Charlie through
  • Eddie and they're both still updating
  • now what's happening this life so that
  • we can't see the one that we've hidden
  • away it's updating as well but we don't
  • care that it's updating now what if you
  • want to have your things on different
  • sheets I'll insert a new worksheet here
  • all iw and I'll take one of these pivot
  • tables maybe the second pivot table and
  • move it to other sheet so ctrl C to copy
  • the pivot table ctrl V to paste the
  • pivot table here and if I need to have a
  • slicer here don't insert a slice from
  • this pivot table we have to come back to
  • our dashboard take the slicer that's the
  • controlling slicer ctrl C make a copy of
  • it and paste it here ctrl V all right
  • now we have no code on this sheet
  • there's no code on sheet 4 and I was
  • thinking I was going to have to add some
  • code to sheet 4 but here's the beautiful
  • thing when I change this slicer what's
  • happening is on the dashboard that pivot
  • tables updating even though that pivot
  • table on that sheet that's not active is
  • updating they will run the code and this
  • will update as well pretty darn amazing
  • that that works now the whole key to
  • this is you can never use the slicer
  • tied to the second pivot table you have
  • to have the slicer this
  • to the second pivot table but you cannot
  • use it you have to use this slicer tied
  • to the first pivot table all right but
  • in general I think this is working
  • fairly well all right now hey Sal the
  • person who asks this question
  • Rodan said look I have a disconnected
  • pivot table are disconnected slicer in
  • the second pivot table only so let's
  • just add new field here called region
  • east-west refresh our second pivot table
  • cool and I'll insert a slicer that is
  • disconnected in other words it's only in
  • the second data set not in the first
  • data set all right now this is gonna be
  • tricky because when I choose East from
  • here we're not going to have anybody
  • selected all right so the pivot table
  • goes away I would have to clear this
  • slicer on the left hand side and then
  • East remain selected but and now things
  • have gone to hell right so you are
  • choosing from a slicer time to the
  • second data set and while the second one
  • is updating the first one is not going
  • to respect that because it has no idea
  • there's no region filled back in the
  • other field this is only going to work
  • when you have the same field in both
  • data sets if you have some other
  • situation like this then it will not fly
  • so here's what you're going to have to
  • do you're going to have to insert that
  • field the region field back in your
  • original data set refresh this pivot
  • table insert a new slicer that will
  • control that first pivot table all right
  • now we have two different slicers now
  • and because I built them backwards their
  • names are backwards this one slice our
  • region one and the one that's going to
  • be the controlling one is called slicer
  • region two I would have planned
  • differently would have had a different
  • result but here we are all f11 I want to
  • take a lot of cutting and pasting them
  • and take those first three lines and
  • paste them change it to slicer cash
  • three slicer cash for slicer item three
  • I'll initialize slicer or attach three
  • and slicer cash for to be region two
  • reason one clear the manual filter on SC
  • force that was a copy and paste take
  • this entire
  • loop here and paste it there are a lot
  • of places you have to change your si 3
  • SC 3 and then SC 4 si 3 si 3 don't miss
  • that one I missed that one next si 3 all
  • right so now this set of code will
  • hopefully control two sets of slicers if
  • you had a third set of slicers you're
  • going to do the same changes I just made
  • copying and pasting and changing things
  • carefully carefully and again now this
  • this guy is the one that we will never
  • see we never want to see that one work
  • because the ones on the pivot table one
  • are the controlling ones so this we have
  • to copy this control see I go to our
  • sheet where we're hiding things away so
  • home format hide none hide/unhide that
  • sheet the dark cave paste so it
  • continues to exist it has to live
  • somewhere and then once I know it's back
  • there on the dark cave I can delete it
  • and then hide this sheet here alright
  • and so now we should have on our
  • dashboard one set of slicers which you
  • Central they both update we choose just
  • flow
  • they both update I clear the filter and
  • central stays that's actually good I'm
  • glad that work clear this filter and
  • everybody comes back but these all have
  • to be driving off that first pivot table
  • what if you have a field in the second
  • data set that's not in your first data
  • set and all bets are off we'll go back
  • to I don't know how to solve that
  • well hey macros game to the solution
  • today and macros are amazing and awesome
  • if you want to learn all about macros
  • Tracy Sears dad and I have written this
  • great book Excel 2016 VBA and macros
  • check that out click the I on the top
  • right hand corner get to your page where
  • you can buy that book
  • alright episode recap how can you have a
  • slicer drive to pivot tables if they
  • both came from the data set it's simple
  • slicer report connections choose other
  • pivot tables but as a pivot table came
  • from two data sets lots of steps change
  • XLS x2 xlsm change your macro security
  • setting all f11 to get the VBA control R
  • to display the project Explorer find the
  • worksheet name that contains your first
  • PivotTable in place our right-click and
  • say view code and then insert code for
  • worksheet update then really important
  • hide that second place our way on a
  • hidden worksheet or far out to the right
  • so no one can ever choose from that
  • slicer by the way don't cut that slicer
  • you have to copy it and paste and then
  • delete the first one in order to get it
  • to work
  • want to thank you for stopping by we'll
  • see you next time for another net cast
  • from MrExcel

Download File

Download the sample file here: Podcast2104.xlsm

Title Photo: Engin_Akyurt / Pixabay