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.
dunkin1969Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.