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

 

[Gheen]

Hello,

I am using Excel 2010 and would like to have my pivot table reference to other areas in my workbook, but cannot get hyperlinks to work for me. For example, I have a large database on Sheet 1 with multiple columns of data, but my pivot table, on Sheet 2, is only needed for certain information. However, I'd like to be able to click on the Service Order Number (the last drill down of my table) and be sent automatically to the correspond row of data for that service order on Sheet 1, just like a hyperlink would do if you reference to a cell in your file. Is there a way or a macro to do this? Most of the help with pivot table hyperlinks takes you to a website, not another cell.

Thanks in advance.

 

[Jerry Sullivan]

Hi Gheen,

The code in this thread might be of help to you...

http://www.mrexcel.com/forum/excel-...able-source-data-rather-than-showdetails.html

Using it, if you double-click on a PivotTable data field item, instead of showing the standard drill down table in a new sheet, it will go to the data source for the Pivot and autofilter the range to show those rows.
The code assumes your data source has a field named "Index" with unique id's.

 

[Gheen]

Hi Jerry, and thank you for the quick reply.

I read through the other thread and I must humbly admit that I am cleary not nearly as advanced in Excel as the two of you are. I have changed the field to "Index" and verified that they all have unique IDs, so I feel this code will work for me... but where do I put it? Should it be a Macro, should I Alt+11 and paste it as a code?

 

[Jerry Sullivan]

Hi Jerry, and thank you for the quick reply.

I read through the other thread and I must humbly admit that I am cleary not nearly as advanced in Excel as the two of you are. I have changed the field to "Index" and verified that they all have unique IDs, so I feel this code will work for me... but where do I put it? Should it be a Macro, should I Alt+11 and paste it as a code?

My apologies. I did a search on Drilldown + AutoFilters to find a few threads on the topic and I pasted a link to the wrong one.

Instead, use the code in Post #8 of this thread...
http://www.mrexcel.com/forum/excel-...ttable-double-click-behavior.html#post3811859

The post describes which VBA Code Module each part of the code needs to be pasted:

Standard Code Module: This is where you typically paste code for macros.
ThisWorkbook Module: You can find this in the Project Explorer of the VB Editor
Sheet Code Module: You can find this for each separate sheet in the Project Explorer of the VB Editor

 

[coral51talk]

Hello everyone,

Can anyone please help me. I am trying to filter names of employees dependent to a cell reference. Somehow, I've been trying to search for a simpler way to do it so as not to go with VBA codes as I am not really well educated with VBA.

I have about 5 pivot tables that has the same row labels. I want to filter all these labels only once by using a single data validation cell outside of the pivot table (as opposed to selected the filter from a drop-down menu). I've been reading through different blogs however I am unable to come up with the code that I need.

The data validation is located on cell B2; the row labels are located on row 2, 24, 46, and the sheet name is Pivot Sheet.


I would really appreciate your assistance on this matter.
Thanks in advance!

 

[Historik]

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

Hi Jerry,

How should I use this VBA code when I have multiple report fields to adjust? For example, I have A1 and A2 which needs to be linked to two different report filters.

Thanks in advance!

Kind regards,

Robin

 

[Historik]

Hi Jerry,

How should I use this VBA code when I have multiple report fields to adjust? For example, I have A1 and A2 which needs to be linked to two different report filters.

Thanks in advance!

Kind regards,

Robin

Here is an example of what I mean. The input, entered in the yellow marked cells, should be exact the same as the report fields.

 

[Always need help]

I have a similar thing but not sure the fix is the same thing. I have a large set of data. There are multiple rows that contain the same data and I created a cell that has the correct count of the items (removing the duplicates). I want to create a pivot table that shows them individually which I have done but the count shows the wrong value and if I do sum they show up as 0. Is there a way for the count to be taken from another column so the count is correct?

 

[teauxja]

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

This was working great....but I renamed the worksheet - updated the CODE and now it's not working. Any suggestions?

 

[esbkaz]

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

Guys, This bit of code made my day! Thank you so much.

Now here is another problem, what if I want the filter on the rows of the pivot table to change based on what I choose in my Target cell?

Karen