PDA

View Full Version : Pivots Table filter



yewee
2014-08-13, 03:52 AM
Hi,

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?

Thanks.

JeffreyWeir
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.

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

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

JeffreyWeir
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.

snb
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

snb
2014-08-17, 04:27 PM
@Jeef

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

JeffreyWeir
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.

snb
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

Fx.Hadi
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