how to set selected slicer or filter items on one pivot equal to another

[naval_oranges]

I have two pivot tables, both of which source the same sheet of data. Each record in the data has two fields, Region ID of person and Region ID of facility, that reference the same list of Region IDs via vlookup (it's just numbers 1-12). Either one or both can be blank. One pivot outputs counts by person Region ID, and the other, by facility Region ID.

Using a combination of nested IF statements and vlookups, I tried making a third Region ID field that could be used as a slicer to control both tables, but what I end up with is an undercount in one of the tables. The only thing that works so far to output the correct counts is having two separate slicers, the facility Region ID being the slicer for one and the person Region ID being the slicer for the other. If the end user wants to see counts for one Region ID, they have to manually set one slicer equal to the other. But what we want is just for the end user to be able to control both tables just by pushing a single number, Region IDs 1-12.

I can make one of the slicers hidden but then how do I get the hidden slicer to automatically select Region ID values equal to the nonhidden slicer? Alternatively, I could somehow program a combo box or list to control the two different slicers, then the slicers would be hidden and the user would see only the combo box/list. (I guess in either of these alternatives, I could just use a report filter instead of a slicer; either way, I still have to get items in one to automatically select based on the user's selected items in another).

FYI, some of the options I looked up involved PowerPivot, which I do not have access to. VBAs/macros are ok, and I would think are the only way to solve the question.

 

[Andrew Poulsom]

Welcome to MrExcel.

You can filter multiple pivot tables with a single slicer:

http://www.contextures.com/excelpivottableslicers.html#videofiltertables

 

[naval_oranges]

Thanks for the response. Unfortunately I already did this; while it initially appeared to work, what we discovered is that either one of the tables will always have an undercount, because it's the same field controlling both tables.

We have two fields, and while they take on the same values (numbers 1-12) and therefore LOOK like one single slicer, they are not. One is the Region ID for a facility, and another is Region ID for a person. So one record, for example, may be blank on the facility but may have, say, number 3 for person Region ID. I tried consolidating the fields into one single Region ID field so I could use it as one slicer to control multiple pivot tables, as you suggested, but many records have two different nonblank values, say a 3 for facility and an 8 for person. This is the population that gets dropped when using the single slicer method, so that one of the tables always produces an undercount.

It works with two different slicers, one for facility Region ID and one with person Region ID. But we need it to look like one slicer (or combo box, or whatever) to the user. To them, it's just numbers 1-12, but what they pick needs to control both slicers. Thoughts?

Welcome to MrExcel.

You can filter multiple pivot tables with a single slicer:

http://www.contextures.com/excelpivottableslicers.html#videofiltertables