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.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

snb

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
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:

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
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
 

snb

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
@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

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
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

New member
Joined
Sep 15, 2014
Messages
1
Reaction score
0
Points
0
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
 
Top