Use another cell on the worksheet for the link target To quickly update all formulas in a worksheet that use a HYPERLINK function with the same arguments, you can place the link target in another cell on the same or another worksheet, and then use an absolute reference to that cell as the link_location in the HYPERLINK formulas. Changes that you make to the link target are immediately reflected in the HYPERLINK formulas.
=HYPERLINK($Z$1) In cell Z1, you then enter the path to the link target.
So you could make a link on Cell 1 on Sheet 1 referring to the link that's on Cell 1 Sheet 2
Or try this solution they mentioned on EE:
Paste the code below into a module (ALT + F11)
Function GetAddress(HyperlinkCell As Range) GetAddress = Replace _ (HyperlinkCell.Hyperlinks(1).Address,""mailto:"", """") End Function
from your worksheet to extract the hyperlink address