Automatically create Hyperlinks based upon links

[j3andc]

I have a summary table of information that is linked to multiple sheets. Each row is linked to a different sheet. Is it possible to automatically create a hyperlink based upon one of the linked cells in each row.

thanks

 

[pboltonchina]

This will creatwe a hyperlink from the cell to the sheet if the cell and sheet name match.

Sub ADMIN_HYPERS()
'==========================================
'DEFINE (& ASSIGN) VARIABLES
'==========================================
Dim s1 As String: s1 = "Sales Order" 'change sheet name to suit
Dim co_1 As Integer: co_1 = 2 'column containing values to assess
Dim rw_1 As Long: rw_1 = 1 'first row in range containing possible hyperlink
Dim rw_2 As Long: rw_2 = Sheets(s1).UsedRange.Rows.Count
Dim h_val As Variant
Dim e As Variant
'==========================================
'LOOP DEPTS & ADD LINKS
'==========================================
Do Until rw_1 > rw_2
    h_val = CStr(Sheets(s1).Cells(rw_1, co_1))
    On Error GoTo Handler:
    e = Sheets(h_val).Cells(1, 1)
    Select Case CStr(e)
        Case "1"
        'do nothing -- error
        Case Else
            Sheets(s1).Hyperlinks.Add Anchor:=Sheets(s1).Cells(rw_1, co_1), _
                Address:="", _
                SubAddress:="'" & h_val & "'!A1", _
                TextToDisplay:=h_val
    End Select
    e = 0
    On Error GoTo 0
rw_1 = rw_1 + 1
Loop
'==========================================
'END
'==========================================
Exit Sub
Handler:
e = 1
Resume Next
End Sub

Function GET_COL(s1 As String, crit As Variant, Rw As Long, m_ord As Integer)
GET_COL = Application.WorksheetFunction.Match(crit, Sheets(s1).Rows(Rw), m_ord)
End Function

Function GET_ROW(s1 As String, crit As Variant, co As Integer, m_ord As Integer)
GET_ROW = Application.WorksheetFunction.Match(crit, Sheets(s1).Columns(co), m_ord)
End Function

 

[Rekd]

I have a summary table of information that is linked to multiple sheets. Each row is linked to a different sheet. Is it possible to automatically create a hyperlink based upon one of the linked cells in each row.

thanks

Yes. But it's hard to help without knowing what/how you're doing it. I'd start by recording a macro, performing the tasks, then edit macro to work the way you want.

 

[j3andc]

The only think that will match is the sheet name in the formula linking back to that sheet. Is it possible to utilize the sheet name from the formula to do that in a macro?