Pivot Table filter on a reference cell

[basima]

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

 

[Jerry Sullivan]

Here is some code for you to try.

Paste this code to the Sheet Code Module of the Sheet that has your DataValidation Cell.

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

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

 

[cyrilbrd]

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!.

 

[Jerry Sullivan]

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!.

Nice to see you back cyrilbrd!

 

[cyrilbrd]

Thanks!
No cellphone signal, no internet, no TV for the past 10 days... It was absolutely great!

 

[Jerry Sullivan]

Thanks!
No cellphone signal, no internet, no TV for the past 10 days... It was absolutely great!

You have 400 Posts since joining the Board in Feb!
Fess up that you missed the Internet a little

 

[basima]

Thank you soooooooooooooooo much. Its all good now!!!

 

[mooseman]

JS411 Solves problems years in advance. Thank you