Create a Hyperlink with Filters

[JNallen]

Hello -
I have 2 sheets and would like to have a hyperlink that takes the user to Sheet2 and applies the filter. I have added =HYPERLINK("#Sheet2!D1","Click here") which does take me to Sheet2 but can not get it to apply a filter to only show relevant data.

Sheet1 :

Business Use Case	Enable Source Table	Enable Source Column	EDM Table	EDM Column	Rpts
	customer_bill_to_master	NATIONAL_ACCOUNT__NAME	F_MERGED_CUST_INVC	NATL_ACCT_NAME	Click here
	enable_base.customer_bill_to_master	NATIONAL_ACCOUNT_NUMBER	D_ITEM_MST	NATL_ACCT_CDE
	CUSTOMER_SHIP_TO_MASTER	shipto_address_line_1	D_CUST_SHIP_TO	ADDR_LNE_1
	CUSTOMER_SHIP_TO_MASTER	shipto_address_line_2	D_CUST_SHIP_TO	ADDR_LNE_2

Sheet2:

Business Use Case	Enable Source Table	Enable Source Column	EDM Table	EDM Column	Rpts
	customer_bill_to_master	NATIONAL_ACCOUNT__NAME	F_MERGED_CUST_INVC	NATL_ACCT_NAME	Click here
	enable_base.customer_bill_to_master	NATIONAL_ACCOUNT_NUMBER	D_ITEM_MST	NATL_ACCT_CDE
	CUSTOMER_SHIP_TO_MASTER	shipto_address_line_1	D_CUST_SHIP_TO	ADDR_LNE_1
	CUSTOMER_SHIP_TO_MASTER	shipto_address_line_2	D_CUST_SHIP_TO	ADDR_LNE_2

When clicking the hyperlink I would like to see only the filtered data by table name. Right now when clicking I go to Sheet2 and see all the rows. Does anyone have a quick way to add this to the hyperlink syntax? I am going through some other posts I see in the forum and any help would be greatly appreciated. Thank you!!

 

[John_w]

This can be done with the Worksheet_FollowHyperlink event handler, but this event is triggered only by hyperlinks created via Insert -> Link, not by hyperlinks created with the HYPERLINK function.

Insert the hyperlink on Sheet1 and paste this code into the Sheet1 module:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ws As Worksheet
    If Target.SubAddress <> "" Then
        Set ws = Evaluate(Target.SubAddress).Worksheet
        Filter_Sheet ws
    End If
End Sub

Worksheet_FollowHyperlink extracts the worksheet object from the clicked hyperlink and calls the Filter_Sheet procedure with it. Paste this code into a new standard module:

Public Sub Filter_Sheet(ws As Worksheet)

    ws.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="=CC", Operator:=xlAnd

End Sub

I don't understand how you want to filter Sheet2, so my Filter_Sheet simply filters column A with the text value "CC". If you explain in more detail exactly how Sheet2 should be filtered I can modify the code.

 

[JNallen]

Thanks John. Sorry for the confusion, I was pasting things way to fast and didn't even paste correctly. Below is Sheet1 & Sheet2 again. We would like to click the hyperlink on sheet1 and have it take the user to Sheet2 filtered by Table Name (Sheet1 - EDM Table). There are thousands of Sheet 1 and the corresponding rpts and the filtering would just same some manual steps.

Sheet1 :

Business Use Case	Enable Source Table	Enable Source Column	EDM Table	EDM Column	Rpts
	customer_bill_to_master	NATIONAL_ACCOUNT__NAME	F_MERGED_CUST_INVC	NATL_ACCT_NAME	Click here
	enable_base.customer_bill_to_master	NATIONAL_ACCOUNT_NUMBER	D_ITEM_MST	NATL_ACCT_CDE
	CUSTOMER_SHIP_TO_MASTER	shipto_address_line_1	D_CUST_SHIP_TO	ADDR_LNE_1
	CUSTOMER_SHIP_TO_MASTER	shipto_address_line_2	D_CUST_SHIP_TO	ADDR_LNE_2

Sheet2:

report_name	db_table	object_name	field_name
Consolidated Delivery Detail – FedEx Office	F_MERGED_CUST_INVC	Customer Header PO	NATL_ACCT_NAME
Order History Detail CS	F_MERGED_CUST_INVC	Customer Header PO	CUST_HDR_PO_NBR
Purchase History Detail	D_ITEM_MST	Customer Header PO	CUST_HDR_PO_NBR
Purchase History Detail (Internal Use)	D_CUST_SHIP_TO	Customer Header PO	ADDR_LNE_1
Purchase History by Manufacturer - CS	D_CUST_SHIP_TO	Customer Invoice Number	ADDR_LNE_2

Thanks for the help! I am certainly not a VBA guru so any guidance is Awesome!

 

[John_w]

It's still not clear exactly how you want Sheet2 to be filtered, so this code is a guess.

Do you want filter column B (db_table) on Sheet2 by all the values in column D (EDM Table) (D2:D<last row>) on Sheet1? In your example the values to filter are F_MERGED_CUST_INVC, D_ITEM_MST and D_CUST_SHIP_TO.

Code in Sheet1 module:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim ws As Worksheet
    Dim EDMtables As Range
    
    If Target.SubAddress <> "" Then
        With Me
            Set EDMtables = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
        End With
        Set ws = Evaluate(Target.SubAddress).Worksheet
        Filter_Sheet ws, Application.Transpose(EDMtables.Value)
    End If
    
End Sub

Code in a standard module:

Public Sub Filter_Sheet(ws As Worksheet, db_tables As Variant)

    ws.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=db_tables, Operator:=xlFilterValues

End Sub

If I'm wrong, please describe precisely how Sheet2 should be filtered.

 

[JNallen]

Yes, you are correct we wanted to filter on the EDM Table matching db_table on sheet2. The scenario is that when we use the hyperlink its to only show the rows of data that use that table in reports. So in row 1 for instance (F_MERGED_CUST_INVC), if I click the hyperlink I would like it to show me all the reports in Sheet2 that match that table in db_table. In the example above I would expect to see the 2 rows at the top after filtered. Instead of all values it would be only the values that match the table name. Thanks for your help!

 

[John_w]

My understanding now is that you want to filter only by the single EDM Table value that's in the same row as the hyperlink.

Sheet1 code module:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    'Filter the hyperlink's target sheet by the value in column D in the same row as the hyperlink
    
    Dim targetWs As Worksheet
    
    If Target.SubAddress <> "" Then
        Set targetWs = Evaluate(Target.SubAddress).Worksheet
        Filter_Sheet targetWs, Me.Cells(Target.Range.Row, "D").Value
    End If
    
End Sub

Standard module:

Public Sub Filter_Sheet(ws As Worksheet, db_table As String)

    ws.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=db_table, Operator:=xlAnd

End Sub

 

[JNallen]

Awesome!! That's perfect, Thank you!