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?
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
CODE
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
Thanks for the tip. Where would I enter this code? Somewhere in excel?
RE: Hyperlink based on text string
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Hyperlink based on text string
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
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
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.
RE: Hyperlink based on text string
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