View Full Version : Pivots Table filter

2014-08-13, 03:52 AM

I have 3 sheets in my excel worksheet.

1. Org
2. DataSource
3. Pivots Table

My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.

How can I do that?


2014-08-15, 10:33 PM
If you have Excel 2010 or later, then instead of using Data Validation in a cell and hooking that cell up to some PivotTables via macros, you can do this non-macro simple alternative:1. Create a new Pivottable, and drag the field you want to filter the other PivotTables by into the PageField. So now you have a PivotTable masquerading as Data Validation across two cells, and looks like this: Country (All)2. Connect that PivotTable to the other PivotTables with a Slicer.That's it...job done.

2014-08-16, 03:21 PM
yewee...read this: http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

2014-08-16, 06:23 PM
I tweaked Jeff's code a 'little' bit.
I wasn't allowed to comment in DDoE.

2014-08-16, 09:49 PM
THat's more consice, snb. Wrote my code at midnight, and didn't spend enough time shortening it. Note that CommandBar(51) isn't the correct commandbar, though. So the AddSlicer command isn't available from the right click PivotTables menu.

2014-08-16, 10:48 PM
In Excel 2010 the correct indexnumber is 51
Maybe you use 2013 ? Please check it's indexnumber.

MsgBox Application.CommandBars("PivotTable Context Menu").Index

2014-08-17, 04:27 PM

In the attachment you'll find an overview of the commandbar indices in Excel 2010.

If you run the macro the Excel 2013 indices will be added in column C

2014-08-17, 09:03 PM
Thanks snb. Any chance you can extract the CommandBar Names in whatever non-English versions of Excel you have at your disposal? I'm interested to see if the names change much, if at all.

2014-08-18, 09:01 AM
I consulted my dear Dutch colleagues:

- Commandbar names only appear in (US) English
- the Indexnumbers appear to be different for every user, independent of the Office version.

So you were quite right to rely on the commandbar names exclusively

2014-09-15, 03:50 PM
Hello every body

I have a problem in my excel project of my university,please help me
I attached the excel file

We have 4 columns and 2 sheets
Date , Hour , Rate 1 , Rate 2
My important date is D and E columns.And I want to calculate Max of each day and also Min of each day and the result automatically write in Sheet 2 when I write the date in sheet 2
Now I want to calculated automatically Max and Min of each day when I write the date in sheet 2

Please help me to solve this problem
Thanks a lot

Be Happy26572658