×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Hyperlink based on text string

Hyperlink based on text string

Hyperlink based on text string

(OP)
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?

RE: Hyperlink based on text string

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.

CODE

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
 

RE: Hyperlink based on text string

(OP)
bacon4life

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

RE: Hyperlink based on text string

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) & "*"

CODE

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

RE: Hyperlink based on text string

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources