Search code examples
excelgoogle-docs

How to create dynamic hyperlink in excel?

I want to link to a string (for a table of contents). But when I create a new line, then the link moves out. How to fix a link so that it always leads to the same place and remains in the same place when new lines are added?

enter image description here

I attach a GIF for a clearer explanation of the problem

Solution

  • Use HYPERLINK and CELL

    To get a reference to a cell you can use this format for your hyperlink:

    #gid=0&range=C1
or
#gid=0&range=E6
or
#gid=0&range=Z10

    So using that information:

    In Cell A1:

    ="#gid=0&range=" & CELL("address", C1)

    This returns #gid=0&range=$C$1 but we want only C1 so we use SUBSTITUTE:

    ="#gid=0&range=" & SUBSTITUTE(CELL("address", C2),"$","")

    To substitute "$" for "", which gives #gid=0&range=C1

    Here you are concatenating the string "#gid=0&range=" together with the result of CELL which returns the address of a cell, except its dynamic!

    In Cell B1:

    =HYPERLINK(A1, "LINK")

    Since the link comes from the dynamic result of A1 it is updated dynamically.

    To get this all in one cell:

    In A1:

    =HYPERLINK("#gid=0&range=" & SUBSTITUTE(CELL("address", C1),"$",""), "LINK")

    Modify C1 to the reference you want.

    Docs