Hyperlink based on text string 1

[rockman7892]

I have two spreadsheets, sheet #1 and sheet #2. In the first sheet I have a column that I have a mechanical equipment tag say "2W01-G3". In the second sheet I have a number of various electrical equipment tags some of whcih match the "2W01" prefix and others which dont.

If in the first spreadsheet I want to create a hyperlink so that when I click on the mechanical tag such as "2W01-G3" it will link to the second sheet and only show those electrical tags that start with 2W01 but not any tags that start with any other prefix.

Is it possible to create this type of hyperlink based on matching part of a string, and then only displaying those items in the second sheet while ignoring the rest?

 

[bacon4life]

This code would do it all in one sheet, but I think just changing ActiveSheet to Sheet2 would do just about what you want. If you need help with any parts of the code, let me know.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    With ActiveSheet
           Dim filterColumn As Integer
           Dim textValue As String
           textValue = "*" & Left(Selection.Value, 4) & "*"
           filterColumn = CInt(Selection.Column)
           
           .AutoFilterMode = False
           .Range("A1:IV1").AutoFilter
           .Range("A1:IV1").AutoFilter Field:=filterColumn, Criteria1:=textValue
    End With
End Sub

 

[rockman7892]

bacon4life

Thanks for the tip. Where would I enter this code? Somewhere in excel?

 

[IRstuff]

It's a VBA macro

TTFN

FAQ731-376

 

[bacon4life]

open tools-macro-visual basic editor
go to ThisWorkbook in the project manager window and paste in the code.

I rearranged the code a bit so that it autofilter sheet2 when you double click in sheet1 (or really any sheet in the workbook).

Right now the following line autofilters anything containing the first four characters in the cell you double click. If you want the cells to start with the string, just remove the first *.

textValue = "*" & Left(Selection.Value, 4) & "*"

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim filterColumn As Integer
    Dim textValue As String
    textValue = "*" & Left(Selection.Value, 1) & "*"
    filterColumn = CInt(Selection.Column)
    With Worksheets("Sheet2")
        .AutoFilterMode = False
        .Range("A1:IV1").AutoFilter
        .Range("A1:IV1").AutoFilter Field:=filterColumn, Criteria1:=textValue
    End With
End Sub

 

[rockman7892]

bacon4life

I really appreciate the help!

My visual basic skills are a little bit rusty, but I pretty much understand the concept of your code. Thank you.

I am not familiar with using this VB macro in excel, and after playing around with the VB editor for a while, I cannot firgure out how to link the code to a particular cell, or column of my spreadsheet. After the code is compiled, do you have to link it to a particular area of the spreadsheet.

The attached workbook contains the two spreadsheets I am working with. Ideally I would like to be able to click on any cell in column A of the "Group 1" spreadsheet and have all the items in the second "Sheet 1" that match the string be shown.

 

[brengine]

rockman7892,

The attached will work for any cell selected in the row (not just the first column) on sheet "Group 1", and filter sheet "Sheet1" accordingly.

brengine