Forum Discussion
Hyperlink or Link between two tabs in the same document
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!
5 Replies
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
adversi, Thanks for your reply, I got it working now. Thanks!
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))
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
- Thanks, it works with this one. With the Hyperlink function as mentioned in your second post I somehow don't get it working. The defined names option will work, thanks!
