Pivot Table filter on a reference cell

basima

New Member
Joined
Mar 29, 2012
Messages
6
Hello everyone - please help. I have an expense report - with expenses for all employees. I have created one big pivot table where you the employee can filter on their employee ID. I would like to make it where they enter their ID on a seperate sheet and the pivot filters on that cell (the cell is the reference for the pivot table). thank youuuuuuuuuuuuuuuuuuuuuu
 
Here is some code for you to try.

Paste this code to the Sheet Code Module of the Sheet that has your DataValidation Cell.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sField As String, sDV_Address As String
   
    sField = "GID"  'Field Name
    sDV_Address = "$C$2" 'Cell with DV dropdown to select filter item.

    If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
        Target.Cells.Count > 1 Then Exit Sub
    
    On Error GoTo CleanUp
    Application.EnableEvents = False

    Call Single_Page_Filter(Sheets("Expense") _
        .PivotTables(1).PivotFields(sField), Target.Text)
    Call Single_Page_Filter(Sheets("Actuals") _
        .PivotTables(1).PivotFields(sField), Target.Text)
       
CleanUp:
    Application.EnableEvents = True
End Sub


Paste this code into a Standard Code Module
Code:
Public Function Single_Page_Filter(pvtField As PivotField, _
        ByVal sValue As String)
    
    On Error GoTo ErrorHandler
    With pvtField
        .ClearAllFilters
        .CurrentPage = sValue
    End With
    Exit Function

ErrorHandler:
    Select Case Err.Description
        Case "Application-defined or object-defined error"
            MsgBox "PivotItem: " & sValue & " not found in PivotTable: " _
                & pvtField.Parent.Name
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
End Function
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Looks like cyrlbrd is offline, so I'll try to answer your question.

thanks for this JS411, I did take a break away from civilization...;)
Anyway looks like you gave Basima a more appropriate answer, although he didn't comment yet.

Cheers!.
:)
 
Upvote 0
Thanks!
No cellphone signal, no internet, no TV for the past 10 days... It was absolutely great!
;)
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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