• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

How Do I Apply Rules to Excel Pivot Table Filters

Hello,

Is it possible to apply a "rule" to an Excel Pivot Table filter?

Pivot Table
I have three filters on my Pivot called 30day, 60day, and 90day.  Each is populated with 'Y' or 'N'.  If a user selects 60day = Y, then 30day and 90day must be reset to (All).... If the user selects 90day = Y, then 30day and 60day must be reset to (All)....  etc.   This way only ONE filter can be applied at a time for 30day, 60day, 90day.

I hope I explained this well.  Thank you.
0
dunkin1969
Asked:
dunkin1969
  • 7
  • 4
  • 2
  • +1
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try something like this (EDITED in the worksheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B11"), Target) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.PivotTables(1).PivotFields("60day").CurrentPage = "(All)"
        ActiveSheet.PivotTables(1).PivotFields("90day").CurrentPage = "(All)"
        Application.EnableEvents = True
    ElseIf Not Intersect(Range("B12"), Target) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.PivotTables(1).PivotFields("30day").CurrentPage = "(All)"
        ActiveSheet.PivotTables(1).PivotFields("90day").CurrentPage = "(All)"
        Application.EnableEvents = True
    ElseIf Not Intersect(Range("B13"), Target) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.PivotTables(1).PivotFields("30day").CurrentPage = "(All)"
        ActiveSheet.PivotTables(1).PivotFields("60day").CurrentPage = "(All)"
        Application.EnableEvents = True
    End If
End Sub

Open in new window

Regards
0
 
dunkin1969Author Commented:
Thank you Rgonzo1971,
I should have mentioned that I am very Excel beginner.  How would I apply this in "module"?
0
 
Rgonzo1971Commented:
pls send a dummy
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dunkin1969Author Commented:
here is a dummy file
Book1.xls
0
 
Patrick MatthewsCommented:
I don't think that's going to work: when you change one of the report filters, it appears that the entire PivotTable gets scooped up in to the change Target.

The following seems to work, but requires a change in approach:
1) Use a separate Data Validation drop-down to indicate the period type you want to filter on
2) Code on the SheetChange event then modifies the PivotTable to modify which PivotFields appear in the report filter

The relevant code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim UsedField As String
    
    If Not Intersect(Target, Me.[Period]) Is Nothing Then
        UsedField = Me.[Period].Value
        With Me.PivotTables(1)
            On Error Resume Next
            With .PivotFields("30day")
                .ClearAllFilters
                .Orientation = xlHidden
            End With
            With .PivotFields("60day")
                .ClearAllFilters
                .Orientation = xlHidden
            End With
            With .PivotFields("90day")
                .ClearAllFilters
                .Orientation = xlHidden
            End With
            On Error GoTo 0
            With .PivotFields(UsedField)
                .Orientation = xlPageField
                .Position = 1
            End With
        End With
    End If
    
End Sub

Open in new window


Sample file attached.
Q-28634790.xlsm
0
 
Rob HensonFinance AnalystCommented:
Will your true data really have a "Y" under more than one column or would it just be one occurance of Y per line?

If so, you could add a column to the right of the data which will return the 30, 60 or 90 Day value and then you can just use that column to filter on.

Formula in G2, copied down:

=INDEX($C$1:$E$1,MATCH("Y",$C2:$E2,0))

Thanks
Rob H
0
 
dunkin1969Author Commented:
Rob,
The true data does allow Y to appear in all three columns.  
But due to unique business environment, the data makes no sense if the user selects more than one Y for the 3 columns.  I want to limit the user to filter only ONE of the three columns at a time.

thanks everyone for the help.  I will try all suggestions.
0
 
Rob HensonFinance AnalystCommented:
Alternative, highlight to the user that they have entered incorrectly.

In a cell near the inputs, eg D10 in your sample, add the following formula:

=IF(COUNTIF($B$11:$B$13,"Y")>1,"Error Message","")

I have then Merged D10 to G13 to make a reasonably large cell and have applied Conditional Formatting such that when it is not blank, it highlights in a colour of your choice.

See attached.

Rob
Pivot-with-CF.xls
0
 
dunkin1969Author Commented:
Thanks Rob.  That's a good alternative by using the "error message" route.

I'll wait and see if others have a way of doing this via a rule or data validation.

Just to reiterate, here is example of what I'm trying to do:

User filters 60day = Y

pivot1
Now user filters 30day = Y... so I want Excel to automatically reset 60day = (All) :

pivot2
This rule would make sure only one Y is filtered at a time for the three columns.
0
 
Rob HensonFinance AnalystCommented:
Another option would be to rearrange your data so that the time entries are in column. You would then have:

City             Location     Time       Freq
Chicago     Red              30Day     100
Chicago     Red              60Day     100
Chicago     Red              90Day     100
Chicago     Green                          125

For those with yes under a time scale you would have an entry for each time, for those with no yes entry time scales an entry with blank or NA in the time column. I hope you see the idea.

Standard database format would normally have variable entries for records in a column rather than a column for each possible variable.

You can then use a single pivot filter on the Time column.
0
 
Patrick MatthewsCommented:
dunkin1969,

Have you even looked at my suggestion?

Patrick
0
 
dunkin1969Author Commented:
Sorry Patrick,
I'm not sure I understand what your example is doing.  Your Pivot Filter only has Location and 60day.  I need 30day and 90day there as well.  

pivot1
pivot2
Thank you
0
 
Rob HensonFinance AnalystCommented:
Not wishing to tread on Patrick's toes but can answer your question; maybe he is offline at the minute.

On Patrick's file there is a Data Validation cell above the Pivot with the three options in the drop down list.

When you change the entry in this cell, it will refresh/rebuild the pivot removing the page field and putting a new page Field for the relevant Day setting and setting it to Y.

Thanks
Rob H
0
 
dunkin1969Author Commented:
ahhh, thanks for the clarification Rob.  I'll check it out in further detail.  Have a great weekend!
0
 
dunkin1969Author Commented:
Thanks Patrick, I took Rob's clarification and your method works for me.  Thanks again to ALL who provided input.  I REALLY appreciate it!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now