Link pivot table report filter to another cell

[richiet]

Hello

Is there a way to link the report filter value in a pivot table to another cell?

I have several pivot tables and rather than having to change the supplier code in the report filter on each one I would like to be able to enter a supplier code in a separate cell and use this to change the filters on all pivot table at once.

thanks for any help

Richie

 

[Jerry Sullivan]

Hello Richie,

You could try this Worksheet_Change code.
Copy it into the sheet code of the worksheet that has the Cell you will use for the filter.
The example below assumes that is B2, and you can edit this to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("B2").Address Then Exit Sub
    Dim PT As PivotTable
    Dim ptItem As PivotItem
    On Error Resume Next
    For Each PT In Worksheets("Sheet1").PivotTables
        With PT.PivotFields("MyReportField")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            Set ptItem = .PivotItems(Target.Value)
            If Not ptItem Is Nothing Then
                .CurrentPage = Target.Value
            End If
        End With
    Next
End Sub

 

[MRomulus]

Hi,

I have a question somewhat related to richiet's. I am trying to get the value of whatever I filter for to show up in a linked cell outside of the respective table. For example, I have a regular table (not pivot table) in Excel and when I select something a value in the filter, I would like that value to appear in a cell outside of the table. The purpose of this is I would like to reference the linked cell for various titles and further calculations.

Thanks in advance,

Marlon

 

[Jerry Sullivan]

Hi Marlon and Welcome to MrExcel,

Try the example at this link...

Rob on Programming: Excel: Displaying Autofilter Criteria

 

[MRomulus]

This is awesome; thank you, Jerry. You can expect to get more questions from me in near future - this is a really helpful site.

Marlon

 

[MRomulus]

I'm running into an issue where the formula I entered in the cell above the filter is not displaying the results. When I click in the cell, it shows the formula up in the formula bar, just no results in the cell -it's just an empty cell. I've tried adjusting the font color and cell background color hoping that it would emerge but no luck. Has anyone encountered this? What could be causing this to happen?

Thanks,
Marlon

 

[Jerry Sullivan]

Marlon, Try this process to narrow down the possible causes of that problem...

Assuming your formula is in Cell A2, place this formula in any empty cell: =LEN(A2)
If the result is 0, it means the formula is evaluating to a null string "" (no characters).

If the result is greater than one, place this formula in any empty cell: =CODE(A2)

Post the results of both test formulas, and also post the formula you are using that isn't displaying an expected result. Include a description of any relevant details such as filters being applied.

 

[Glen_Marshall]

Hi all,

Very simple this I'm sure, below code works like a treat and saves me hours.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("F1").Address Then Exit Sub
    Dim pt As PivotTable
    Dim ptItem As PivotItem
    On Error Resume Next
    For Each pt In Worksheets("Pivot Year").PivotTables
        With pt.PivotFields("County")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            Set ptItem = .PivotItems(Target.Value)
            If Not ptItem Is Nothing Then
                .CurrentPage = Target.Value
            End If
        End With
    Next
End Sub
 
'ptItem = ShowAllItems = true

However I would like it to display 'all' results (so have all of the filter options ticked) if All is selcted in my named range. I think the code I need is something like the commented out bit at the bottom but try as I might I cannot get this to work, any help greatly appreciated.

many thanks

Glen

 

[Jerry Sullivan]

Hi Glen,

Here's some code for you to try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("F1").Address Then Exit Sub
    Dim pt As PivotTable
    Dim ptItem As PivotItem
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    For Each pt In Worksheets("Pivot Year").PivotTables
        
        With pt.PivotFields("County")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            If LCase$(Target.Value) = "all" Then
               .ClearAllFilters
            Else
               Set ptItem = .PivotItems(Target.Value)
               If Not ptItem Is Nothing Then
                   .CurrentPage = Target.Value
               End If
            End If
        End With
    Next
CleanUp:
   Application.EnableEvents = True
End Sub

 

[Glen_Marshall]

Spot on - works a treat - thank you.

This code now saves me c. 3 hours a week (and takes way human error of not changing one of the pivot tables) so very very useful