CODEWITHSUNDEEP
excel
Kode
Kode

Reputation: 3215

Formula to Apply Filter on Excel Table

I have two worksheets in an Excel 2013 workbook. The first has a table of client information and the second is a table of purchases by client. The second table has the "Client ID" tied to each purchase. What I would like to achieve is a link next to each client in the clients table that when clicked sends a user to a filtered view of the purchases in the second table. I believe I can use Excels hyperlink capabilities to link across worksheets, but how would I make it apply a filter to a table?

Many thanks!

K.

Upvotes: 1

Views: 129

Answers (1)

user3514930
user3514930

Reputation: 1717

Following the Scheme. Link Sheet [Sheet...]:

enter image description here

and Data Sheet [Data]:

enter image description here

you can add the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Crit As String

    If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
         If Target.Column = 3 Then
            Crit = Cells(ActiveCell.Row, 1).Value
            If ActiveCell.Row = 1 Then Crit = "ALL"
            If Crit = "" Then Exit Sub

            Sheets("Data").Select
            Sheets("Data").Range("A1").Select
            If Crit = "ALL" Then ' ALL
                Selection.AutoFilter
            Else
                Selection.AutoFilter Field:=1, Criteria1:=Crit
            End If
        End If
    End If
End Sub

In the event selection Change of the Sheet with the list of the names.
The macro consider the Data are on the Sheet "Data".
If you click on the label ALL the macro show all ...

Upvotes: 1

Related Questions