Dynamic Filter between two pivot tables

[HYChau]

Hi, new to the forum here looking for some pivot experts!

I have two pivot tables as seen in the attachment. My goal is to be able to filter "Second Level" and automatically have the second pivot (Rightside) filter the associated "User Groups"

So the end result... if i filtered "Reports (USA)" on the first table, the second table will automatically only show data for the groups Approvers, USA Visitors, and Editors - USA.

Any tips or suggestions are greatly appreciated!

The dummyexcel is attached.

[dflak]

First of all, not everyone can see a PNG file. It's one of the quirks of the forum. So if you are going to post a picture, use JPEG format. Secondly, people are more apt to respond if they have a workbook that they can dig into.

Third, if both pivot tables are based on the same data source, then you can "link" them using slicers. Slicers are a "whistle and bells" version of a filter.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[HYChau]

Thanks for the reply! I attached the workbook.

The pivot tables are connected to different sets of data so I can't use slicer!

[dflak]

I'm going to put the data into Excel tables because they can extend formulas all the way down automatically. We are also going to need a translation table that links "Reports (USA)" to Approvers, USA Visitors, and Editors - USA. I'll need you to fill in this table for me.

[HYChau]

Hi dflak,

Thanks for the reply, I've thought about doing a translation table but there will be probably 500levels for "Approvers", I was trying to find an easier way with less manual work.

Is it possible to change "Pivot 2" into a regular table and use advanced filters based on an extract of "User Groups" of Pivot1?

[dflak]

The problem with Excel is it has no way of know what you mean to say. Even I, as a human, can't figure out what groups line up with what levels. If you have a very strict naming convention such as if it has the string, "USA" it maps to Reports (USA), then it's possible to write logic to figure it out. However without this an explicit map is the only way I can see going.