dynamic updating of hyperlinks between sheets

I am wondering why my hyperlinks breaks between worksheets within the same workbook.

Say I have a worksheet WS1 and I want to create a hyperlink that when I click on the hyperlink, it takes me to a cell on another worksheet (within the same workbook).

So, on WS1, call 1A, I create a hyperlink to a cell 1E on WS2.
This works fine.

Now, I insert a new record on WS2 above row E and my hyperlink no longer takes me to the same row I originally linked it to.
It still takes me to cell 1E, but now that is what was in 1D because it moved down when I inserted a row above 1E.

How can I make the hyperlink dynamically update when a row is inserted above it (as described above).

Same applies in horizontal direction.

I believe someone mentioned that I can create the hyperlink to a specific word instead of a cell reference, but I'm not sure how to do that, or if that would work in my scenario.

With a macro you solve your problem


You can solve easily this problem with a macro.

Imagine that You what the value from sheet2 and cell A1 copied to cell A1 sheet1.

Just create a macro with this code:

Sub Dynamicvalues()
Range("A1").Value = Sheets("sheet2").Range("A1").Value
End Sub

And you will see each time you create a new row, the macro takes the new value on cell A1 sheet2 instead of changing the connection towards A2 ;)

I hope it helps,
Cátia Santos