Hyperlink using Vlookup

[Sakthikumar Chinnasamy]

Hi Guys,
I need ur help to build the vlookup formula using Hyperlink. In the attached excel sheet, there are two sheets - Group Wise & Client Wise. In the Client Wise sheet, If I click the C2 value, then it should populate the corresponding detail from Group Wise using hyperlink.

 

[Chihiro]

Your requirement isn't clear to me.
Do you mean that you want to use vlookup to find corresponding data in "Group Wise" sheet and jump to the cell through hyperlink?

See attached for sample.

 

[Sakthikumar Chinnasamy]

Hi Chihiro,

thank you so much for your reply.

However, instead of jump to the cell i need to display only selected values.

For example, in the client wise sheet, if i click C2 value #cnnjbkup01cnnjbkup01, then in the Group wise Sheet, i want to display only C2 value related details

Server Client Group Client + Server
cnnjbkup01 cnnjbkup01 All servers Daily Diff cnnjbkup01cnnjbkup01
cnnjbkup01 cnnjbkup01 All servers Weekly Full cnnjbkup01cnnjbkup01

 

[Chihiro]

So you want to filter the data on Group Wise sheet based on selection?

It's not possible via formula.

You will need VBA.

See sample attached. Note that I converted both data range to tables.

Code in "Client Wise" worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)) Is Nothing Then
    With Worksheets("Group Wise")
        If .FilterMode Then .ShowAllData
        .ListObjects(1).Range.AutoFilter Field:=4, Criteria1:=Target.Value
    End With
End If
       
End Sub

 

[Sakthikumar Chinnasamy]

Once again thank you for providing the vba formula.. However Is it possible to write a code if i double click the cell value of C from Client wise sheet, it will view automatically the related detail in Group wise sheet

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

Replace previous code with below.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)) Is Nothing Then
    Cancel = True
    With Worksheets("Group Wise")
        If .FilterMode Then .ShowAllData
        .ListObjects(1).Range.AutoFilter Field:=4, Criteria1:=Target.Value
        .Activate
        .Cells(1, 1).Select
    End With
End If
End Sub

 

[Sakthikumar Chinnasamy]

Hi,

I replaced the code in the excel sheet and ran it but i received an error message as "subscript out of range" and it highlighted this line code:

.ListObjects(1).Range.AutoFilter Field:=7, Criteria1:=Target.Value.

I attached the file for your reference. Pls advise on this

 

[Chihiro]

Quote from my second post.

Note that I converted both data range to tables.

Since your Group Wise sheet does not contain table (i.e. ListObject), code errors out at that line.

Solution:
1. Convert your data range to table.
2. Change code to something like below.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F2:F" & Cells(Rows.Count, 6).End(xlUp).Row)) Is Nothing Then
    Cancel = True
    With Worksheets("Group Wise")
        If .FilterMode Then .ShowAllData
        .UsedRange.AutoFilter Field:=7, Criteria1:=Target.Value
        .Activate
        .Cells(1, 1).Select
    End With
End If
End Sub