Dec 15 2020 02:42 AM
Hi,
I am working with Excel in Office365. I have a spreadsheet containing multiple tabs and what I would like to do is create a link between a specific cell (A1) in tab A, to a specific cell (B4) in tab B so that when I click on the cell in tab A I get redirected to the cell in tab B. I understand the easy way of creating a hyperlink, but this fails when I later make changes in tab B . For instance, when I insert a row at the top of tab B, the cell that I want to be referred to will be B5. The reference in the Hyperlink function is not auto adjusted and will keep referring to B4 (which in my case is then empty). I tried fixating with dollar signs, but these are removed when creating the hyperlink.
Does anyone have a solution to solve this?
Thanks in advance!
Dec 15 2020 03:19 AM
You could define a name pointing att B4 (lets call it OldB4)
Use the name-box or [Ctrl]+[F3], New
Goto TabA and insert a Hyperlink-this book->Defined names
Now your hyperlink will always point att OlldB4
Dec 15 2020 04:26 AM - edited Dec 15 2020 04:27 AM
I overcomplicated. Use a formula instead (with or without $):
=HYPERLINK('tab b'!B4,"friendly text")
Or, if you want the visual address of the link to change:
=HYPERLINK('tab b'!B4,CELL("address",'tab b'!B4))
Dec 15 2020 05:17 AM
The best way is to define a name range for Cell B4 in Tab B. See the example below:
1. Define Name
Formulas > Name Manager > New
2. Insert Hyperlink
Ctrl + K (shortcut) >Place in this Document > Defined Names
Now even if you add columns/rows, the defined cell will always be selected
Dec 15 2020 06:18 AM
@adversi, Thanks for your reply, I got it working now. Thanks!
Dec 15 2020 06:20 AM