Results 1 to 7 of 7

Thread: Clicking a hyperlink in Excel to set filter on a different sheet

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clicking a hyperlink in Excel to set filter on a different sheet

    I have an Excel workbook with two sheets, basically a one-to-many setup between the two sheets. The first sheet lists projects and the second sheet lists the issues for each project. The second sheet has filters on each column
    What I am attempting to do is have a user click the issues cell on the first sheet so the user is then taken to the next sheet with the filter already populated with the project selected.
    Here is the format:

    Sheet 1 (Audits). Identifier is under audit name. The issues column contains the hyperlink to the second tab, but I don't know how to have it filter by the audit name.

    Year Audit Name Location Audit Type Issues
    2011 2011 Construction Assessment US/CAN Assessment Issues
    Sheet 2 (Issues). Identifier is in column L

    Issue Risk Management Owner Issue Id Issue Category Key SOX Issue? Issue Local Priority Issue Global Priority Issue Report Date Issue Plan Date Days Overdue Issue Name Issue Description Audit Name
    Is there a quick VBA somebody could share to help do what I am trying to do?
    Last edited by tiltz; Dec 19th, 2018 at 01:27 PM.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,436
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clicking a hyperlink in Excel to set filter on a different sheet

    Hello,

    The quickest way is ...

    Better than a hyperlink : you should use a Double-Click Event macro ...

    which would feed your second sheet with the criteria required in the Filter ...

    and display the filtered results ...

    HTH

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clicking a hyperlink in Excel to set filter on a different sheet

    Thanks. Given sheet formats above, what would that VBA look like?

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clicking a hyperlink in Excel to set filter on a different sheet

    This is what I tried, but it doesn't do anything:

    Private Sub Sheet1_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Cancel = True
    'Update Table2
    'Update Column 2
    'Update Sheet2
    'Change on Column 12
    If ActiveCell.Column = 5 Then
    Audits.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=ActiveCell.Offset(0, -3)
    'Update Sheet2
    Sheet2.Activate
    End If
    End Sub

  5. #5
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,436
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clicking a hyperlink in Excel to set filter on a different sheet

    Hello,

    In the sheet 1 module (i.e Audits ) you could test :

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 2 Then Exit Sub
    Dim last As Long
    last = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
    Sheet2.Range("A1:L" & last).AutoFilter
    Sheet2.Range("A1:L" & last).AutoFilter Field:=12, Criteria1:=Target.Value
    Cancel = True
    Application.Goto Sheet2.Range("A1")
    End Sub
    Hope this will help

  6. #6
    New Member
    Join Date
    Apr 2012
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clicking a hyperlink in Excel to set filter on a different sheet

    Thank you. That worked perfectly

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,436
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Clicking a hyperlink in Excel to set filter on a different sheet

    Quote Originally Posted by tiltz View Post
    Thank you. That worked perfectly
    You are welcome ...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •