Pivots Table filter

yewee

New member
Joined
Nov 12, 2013
Messages
8
Reaction score
0
Points
0
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.
 
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.
 
I tweaked Jeff's code a 'little' bit.
I wasn't allowed to comment in DDoE.
 

Attachments

  • 0_Sync-PivotTables-from-dropdown_20140817.xlsm
    30.2 KB · Views: 24
Last edited:
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.
 
In Excel 2010 the correct indexnumber is 51
Maybe you use 2013 ? Please check it's indexnumber.

Code:
MsgBox Application.CommandBars("PivotTable Context Menu").Index
 
@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
 

Attachments

  • Commandbars indices comparison 2010- 2013.xlsb
    14.7 KB · Views: 14
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.
 
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
 
Max and Min

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 HappyPic Max Toll.pngView attachment EJ-Project1.xls
 
Back
Top