Link pivot table report filter to another cell

richiet

Board Regular
Joined
Apr 24, 2002
Messages
51
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi all,

Very simple this I'm sure, below code works like a treat and saves me hours.
Code:
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
 
Upvote 0
Hi Glen,

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

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,383
Messages
6,165,665
Members
451,983
Latest member
Raph24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top