Hyperlink, Macro and Filtering all in one

**eddiec** · 10-22-2010, 06:29 PM

Hi,

I have a spreadsheet with Dept ID# in one column and a count of employees in that department in a second column on one tab. On another tab, I have employee details by dept. For example, let's say dept #1040 has a count of 3 emps on the first tab. The user would then click the the other tab, which will show all data, but he/she can then filter by the Dept # to pull up all 3 records.

Now for the programming part: On the first tab, I want to create hyperlinks for the counts, which, when clicked, will switch to the second tab, then filter by Dept# to display all emp records related to that Dept#. Attached is a sample spreadsheet...

My initial thought was to use a macro, as I think formula doesn't have the ability to do something this complex, but if there is a more elegant way of doing this, I'm open to suggestions.

Thanks in advance!

**davesexcel** · 10-22-2010, 08:47 PM

Use this formula in B1 and copy it down

=HYPERLINK("#Sheet2!A1",COUNTIF(Sheet2!$A$1:$D$22,Sheet1!A2))

Place this code in Sheet1 Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column <> 2 Then Exit Sub
    Worksheets("Sheet2").Range("A1").AutoFilter Field:=1, Criteria1:=Target.Offset(0, -1)

End Sub

**Leith Ross** · 10-22-2010, 09:22 PM

Hello Dave,

Just thinking that you can activate "Sheet2" in the Worksheet_SelectionChange event and eliminate the Hyperlink formulas.

**davesexcel** · 10-22-2010, 09:36 PM

Thanks Leith,
Had hyperlink stuck in the head

Placing this in B2 and dragged down,
=COUNTIF(Sheet2!$A$2:$A$10,Sheet1!A2)

Then

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    Worksheets("Sheet2").Activate
    Worksheets("Sheet2").Range("A1").AutoFilter Field:=1, Criteria1:=Target.Offset(0, -1)

End Sub

Does the exact same thing.

**eddiec** · 10-25-2010, 11:16 AM

Thanks for the response! I'll give it a try later today and let you know how it goes.

**eddiec** · 10-25-2010, 04:26 PM

It worked! I copied the code and tweaked it a bit to fit the actual spreadsheet, which is MUCH bigger, and it worked!! Thanks for your help on this. [EOM]