Hyperlink or Link between two tabs in the same document

%3CLINGO-SUB%20id%3D%22lingo-sub-1988471%22%20slang%3D%22en-US%22%3EHyperlink%20or%20Link%20between%20two%20tabs%20in%20the%20same%20document%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1988471%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20working%20with%20Excel%20in%20Office365.%20I%20have%20a%20spreadsheet%20containing%20multiple%20tabs%20and%20what%20I%20would%20like%20to%20do%20is%20create%20a%20link%20between%20a%20specific%20cell%20(A1)%20in%20tab%20A%2C%20to%20a%20specific%20cell%20(B4)%20in%20tab%20B%20so%20that%20when%20I%20click%20on%20the%20cell%20in%20tab%20A%20I%20get%20redirected%20to%20the%20cell%20in%20tab%20B.%20I%20understand%20the%20easy%20way%20of%20creating%20a%20hyperlink%2C%20but%20this%20fails%20when%20I%20later%20make%20changes%20in%20tab%20B%20.%20For%20instance%2C%20when%20I%20insert%20a%20row%20at%20the%20top%20of%20tab%20B%2C%20the%20cell%20that%20I%20want%20to%20be%20referred%20to%20will%20be%20B5.%20The%20reference%20in%20the%20Hyperlink%20function%20is%20not%20auto%20adjusted%20and%20will%20keep%20referring%20to%20B4%20(which%20in%20my%20case%20is%20then%20empty).%20I%20tried%20fixating%20with%20dollar%20signs%2C%20but%20these%20are%20removed%20when%20creating%20the%20hyperlink.%26nbsp%3B%3CBR%20%2F%3EDoes%20anyone%20have%20a%20solution%20to%20solve%20this%3F%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1988471%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1988573%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20or%20Link%20between%20two%20tabs%20in%20the%20same%20document%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1988573%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F902281%22%20target%3D%22_blank%22%3E%40PeterSchmeits%3C%2FA%3E%3C%2FP%3E%3CP%3EYou%20could%20define%20a%20name%20pointing%20att%20B4%20(lets%20call%20it%20OldB4)%3C%2FP%3E%3CP%3EUse%20the%20name-box%26nbsp%3B%20or%20%5BCtrl%5D%2B%5BF3%5D%2C%20New%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoto%20TabA%20and%20insert%20a%20Hyperlink-this%20book-%26gt%3BDefined%20names%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20your%20hyperlink%20will%20always%20point%20att%20OlldB4%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1988765%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20or%20Link%20between%20two%20tabs%20in%20the%20same%20document%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1988765%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F902281%22%20target%3D%22_blank%22%3E%40PeterSchmeits%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20overcomplicated.%20Use%20a%20formula%20instead%20(with%20or%20without%20%24)%3A%3C%2FP%3E%3CP%3E%3DHYPERLINK('tab%20b'!B4%2C%22friendly%20text%22)%3C%2FP%3E%3CP%3EOr%2C%20if%20you%20want%20the%20visual%20address%20of%20the%20link%20to%20change%3A%3C%2FP%3E%3CP%3E%3DHYPERLINK('tab%20b'!B4%2CCELL(%22address%22%2C'tab%20b'!B4))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@PeterSchmeits

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

@PeterSchmeits 

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))

@PeterSchmeits 

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

adversi_0-1608037244521.png

 

2. Insert Hyperlink

Ctrl + K (shortcut) >Place in this Document > Defined Names

adversi_1-1608037310893.png

 

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!

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!