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
– MrDogme, Commented Mar 7, 2018 at 22:02
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
– MrDogme, Commented Mar 7, 2018 at 22:19
do you mean something like =HYPERLINK("#'"&A2&"'!A2","Link to "&A2) — chris neilsen
– chris neilsen, Commented Mar 7, 2018 at 22:23
@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
– Kishanu Bhattacharya, Commented Mar 7, 2018 at 22:28

L42 · Accepted Answer · 2018-03-08 02:41:38Z

1

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.

edited Mar 8, 2018 at 2:41

answered Mar 8, 2018 at 2:36

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating hyperlink to a sheet using excel formulas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related