Creating hyperlink to a sheet using excel formulas

Question

I have a column which has values A, B, and C in a Sheet called Demo. There are other three sheets with the same name as the column values. I want to create a hyperlink using excel formula which would refer to the sheets A, B, and C. I tried with this =HYPERLINK("#'"&A2&"'!A2","Link") But this would have another column which will have a friendly name as Link. Is it possible to refer to the same values without using any other column in Sheet Demo?

If I understood well, you wish that cells could contain both a hyperlink formula and a value. Am I correct ? — MrDogme
Well I tought you could not. BUT you can, as long as you can nest the value (or the equation/formula producing this value) into the friendly name. Without quotes. — MrDogme
do you mean something like =HYPERLINK("#'"&A2&"'!A2","Link to "&A2) — chris neilsen
@chrisneilsen Exactly what I ended up doing now. Thanks! Additionally, we have to turn on the iterative calculation from File->Options->Formula->Check the box having label Enable iterative calculation so that it can understand it's a circular reference. — Kishanu Bhattacharya

L42 L42 · Accepted Answer · 2018-03-08T02:36:12

Although, I think it is worth considering VBA too (since you included that tag in your original question).

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo restoreevents
    Application.EnableEvents = False

    Dim sn As String: Const hf As String = "=HYPERLINK(""#'{ref}'!A2"",""{ref}"")"
    With Me
        If Not Intersect(Target, .Range("A2:A4")) Is Nothing _
        And Len(Target.Value2) <> 0 Then
            sn = Target.Value2
            Application.Undo
            Target.Formula = Replace(hf, "{ref}", sn)
        End If
    End With

restoreevents:
    Application.EnableEvents = True

End Sub

Above code will hyperlink using formula once you type the Sheet name on the target ranges.

Creating hyperlink to a sheet using excel formulas

1 Answers