CODEWITHSUNDEEP
excelgoogle-docs
RoyalGoose
RoyalGoose

Reputation: 523

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

Upvotes: 0

Views: 1038

Answers (2)

iansedano
iansedano

Reputation: 6481

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

Upvotes: 1

RoyalGoose
RoyalGoose

Reputation: 523

To prevent the link from moving, it nesseccary to use named ranges. Add a range to the cell that need to refer to. Then right-click in the table of contents - link, make a link through the named range. Then, when adding columns / rows, the named range also changes and the link always leads to the desired cell.

It is also possible not make named ranges, but simply make a reference to the cell. I.e. do not get a link and insert it into the table of contents, but make a link to a cell in the table of contents, then it moves when rows / columns are added

Upvotes: 0

Related Questions