Change pivot table report filter selection from a cell

**nestorph** · 08-16-2011, 10:01 PM

I am trying to create a worksheet that contains several pivot tables that are displaying information from various tables. They all have at least one field in common. On that sheet I would like to have a pulldown list of possible choices for that field. The user would then select the choice from the list and all of the pivot tables would then filter their results against that choice.
I've found a blog where the author (back in 2008) describes how this can be done by writing VBA code. Since then, MS has released Excel 2007, 2010, and 2011. Can what I'd like to do be done more simply with the newer releases or do I have to go the VBA route?

**MarvinP** · 08-16-2011, 11:37 PM

Hi nestorph and welcome to the forum,

The latest version of Excel has a Slicer tool that may do what you want without VBA. I wrote some code a few months ago to do what I think you want. See the attached file for the example.

**nestorph** · 08-17-2011, 07:38 AM

Thanks, Marvin. I'll look into the Slicer tool but in the meantime I checked out your sample. I see the three pivot tables in the Pivot tab. I'm not sure how I'm supposed to apply the selection of the filter from an external cell. Let's say that I have the value "Week 21" in cell A20. How can I get those tables to filter against the value in A20?
I also tried to see what happens when I click on the filter list in the first pivot table (cell B1) to filter from within the pivot table, but I get an error message that reads "PivotTable cannot overlap another PivotTable".
FYI, I'm using Excel 2011.

PS I forgot to mention that after I get that error message, Excel crashes!

Originally Posted by MarvinP

Hi nestorph and welcome to the forum,

The latest version of Excel has a Slicer tool that may do what you want without VBA. I wrote some code a few months ago to do what I think you want. See the attached file for the example.

**nestorph** · 08-17-2011, 09:46 AM

I just checked out Slicers. Turns out they are only available in Excel 2010 (which may explain why Excel 2011 was crashing). Consequently, they're not a solution for me as I need my spreadsheet to be backward compatible to at least Excel 2007 since many of my clients haven't moved to 2010 yet.
So I'm back to my original quest for a way to control the filter of a pivot table from another cell.

**MarvinP** · 08-17-2011, 10:19 AM

In my example file, the idea was to change the row filter at the top of any one of the three pivot tables and it would change all of them to the the same filter.

Sorry I wasn't more specific on how to work it. The VBA code was the hard part.