Happy Thursday! I’ve got two news items today, and you can read the details below.
- a new sample file on my Contextures website
- a Microsoft Consumer Camp event in the Toronto area
Pivot Table Report Filters
There are several sample files on my website that let you change all pivot tables, based on a change to one pivot table. Kevin asked about one sample, which lets you select from a data validation drop down list. In the original file, changing that drop down updated all the pivot tables in the workbook.
Kevin only wanted the pivot tables on one sheet to change, so I created a new sample that does that.
Get the Sample File
In the new version, you can select an item from a data validation drop down, and all pivot tables on that worksheet show that selection in the page field. Pivot tables on the other sheets aren’t affected.
You can download the sample file from my Excel Files page. In the Pivot Table section, look for PT0038 – Change Report Filters With Cell Dropdown Same Sheet
Microsoft Consumer Camp in Mississauga
If you’re in the Toronto/Mississauga area, you can put this event in your calendar — Microsoft is hosting the first Canadian MVP Consumer Camp , and you’re invited. Register here.
- Date: Thursday, May 29, 2014
- Time: 4pm to 9pm
- Location: Microsoft Store at Square One Shopping Centre in Mississauga
Many Canadian MVPs will be there, answering tech questions, showing off demos and the unique features of Microsoft devices. There will be prize draws, Q&A sessions, snacks and refreshments.
Register here!
_______________________
How would you combine this macro with the macro found on PT0025?
Gabe: If you make a copy of one of the pivottables and only have the field you want to filter by as a PageField, and have no other fields in that pivotTable, then what you essentially have is a PivotTable disguised as a data validation dropdown. It only occupies two cells, and you can put it wherever you want. Because it’s a PivotTable, then PT0025 will work with no modifications whatsoever when users select something from that dropdown.
It’s worth noting that if you have Excel 2010 or later AND all your pivots share the same cache (i.e. they all are based off the exact data source), then you can use the approach I suggest above to create a PivotTable that the user will think is just a Data Validation dropdown, and then hook that PivotTable up to whatever others you want to sync using Slicers. Then you don’t need any code whatsoever. Plus you can also use this on row fields and column field if you want to, all at pretty much the speed of light.
@Gabe again: if you don’t have Excel 2010 or your pivots don’t share the same cache, then I suggest you use PT0029 as it’s much faster – which will be particularly noticeable on large pivots. See http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/
Hi jeff so I have a spread-sheet with seven tabs, each tab representing a person, each person has taken seven of the same subjects with the same headings, the only thing that differs is the score and results, I have than created a dashboard, and done multiple consolidated pivot tables. The pivot tables are linked to graphs, I have created. I have than linked all of the pivot table report filter to a validation, that when I select a person on the validation all the pivot table report filters should change, I have amended the code taken from pt038 and its not working, can I get some assistance. Sanjay twist
Hi Debra,
Thanks to you, I have managed to trigger from a page field change an update which basically rebuilds the Pivot Table with refreshed values and a couple of calculated fields … ( Event Worksheet_PivotTableUpdate )
In addition, to minimize the burden, I have also streamlined my Pivot caches (down to 2) throughout my workbook …
As a consequence of above, something rather strange ( strange to me …) is happening :
a standard Refresh of any of my eight pivot tables sends me to another worksheet…?
Why ? Is it the sharing of the same Pivot cache which causes this ? and,
Is there a way to prevent this from happening ?
Thanks a lot
James
Hi Debra, (and Blog readers)
I think I must share this lesson learned the hard way…
When dealing with multiple Pivot Tables combined with their own Event_Update Macros, a certain confusion can be created by the “over-streamlining” of PivotCaches …
By replicating Defined Names for your Source Database, you do increase the workbook size …but you do avoid the Refresh process of all your Pivot Tables to become somewhat “strange” …and very confusing …
I am trying to apply this to consolidated pivot tables, please advice the vba script is not working
I ran your code in PT0038, and I see that all the pivot tables changed except for PT1. Why is that?
I am trying the same code, but my filters did not change, it says multiple items, but when you click the drop down, it’s still sitting on the original selection.
This just does not work with Excel 2013 apparently. Throws errors “Invalid procedure call or argument” in a number of places.
What is the source data for your pivot table? A normal Excel table, or something else?
How could you manipulate this VBA code to show two filters.
Ie the first one “Region” and say another one “Type”.
The region one works perfectly but im pretty new to this VBA and cant get the second to work. I thought copying and pasting the first code just replacing the “SelRegion” and “Region” but it comes up with errors?
Thanks
This vba can be useful in a pivot from Power Query?