Home

How to create a dynamic hyperlink based on a changing cell reference in a different worksheet.

%3CLINGO-SUB%20id%3D%22lingo-sub-658997%22%20slang%3D%22en-US%22%3EHow%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658997%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20I%20am%20working%20in%20an%20excel%20workbook%20that%20has%20two%20different%20worksheets%20(sheet1%20and%20sheet2).%20I%20am%20trying%20to%20create%20a%20hyperlink%20on%20sheet1%20that%20can%20take%20me%20specific%20cells%20in%20sheet2.%20However%2C%20i%20am%20working%20with%20a%20lot%20of%20rows%20and%20i%20do%20not%20want%20to%20create%20a%20hyperlink%20for%20each%20row.%20Please%20does%20anyone%20know%20a%20formula%20that%20i%20can%20use%20once%20and%20able%20to%20drag%20so%20that%20it%20matches%20the%20respective%20cell%20reference%3F%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-658997%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659074%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659074%22%20slang%3D%22en-US%22%3EIf%20you%20want%20to%20link%20to%20the%20last%20non-blank%20cell%20in%20Column%20A%20of%20Sheet2%2C%20you%20may%20create%20this%20formula%20in%20Sheet1%3A%3CBR%20%2F%3E%3DHYPERLINK(%E2%80%9C%23Sheet2!A%E2%80%9D%26amp%3B%3CBR%20%2F%3ECOUNTA(Sheet2!A%3AA)%2C%E2%80%9DSheet2!%E2%80%9D%26amp%3B%3CBR%20%2F%3ECOUNTA(Sheet2!A%3AA))%3CBR%20%2F%3ENote%20that%20the%20name%20of%20the%20link%20in%20the%20foregoing%20formula%20is%20the%20address%20of%20the%20cell%20in%20Sheet2%20to%20which%20the%20link%20will%20jump.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659161%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%20i%20get%20the%20attached%20message%20when%20i%20put%20in%20the%20formula.%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20i%20typed%20in%20%3DHYPERLINK(%E2%80%9C%23Hours!K%E2%80%9D%26amp%3BCOUNTA(Hours!K%3AK)%2C%E2%80%9DHours!%E2%80%9D%26amp%3BCOUNTA(Hours!K%3AK))%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3Esheet2%20is%20hours%3C%2FP%3E%3CP%3EK%20is%20the%20column%20in%20sheet2%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659228%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659228%22%20slang%3D%22en-US%22%3EI%20didn%E2%80%99t%20test%20the%20formula%20because%20I%20replied%20to%20you%20via%20mobile%20phone.%20I%20will%20correct%20the%20formula%20after%20I%20get%20some%20sleep.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659750%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659750%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20formula%20is%20correct!%20I%20surmise%20you%20copied%20and%20pasted%20my%20suggested%20formula%2C%20and%20then%20you%20modified%20it.%20Instead%2C%20please%20type%20this%20formula%20yourself%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DHYPERLINK(%22%23Hours!K%22%26amp%3BCOUNTA(Hours!K%3AK)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22Hours!K%22%26amp%3BCOUNTA(Hours!K%3AK))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20suspect%20the%20formula%20might%20have%20included%20some%20invisible%20characters%20when%20you%20copied%20and%20modified%20it.%20Also%20note%20that%2C%20in%20my%20originally%20suggested%20formula%2C%20I%20inadvertently%20omitted%20the%20Column%20Letter%20in%20the%20friendly_name%20argument%20of%20HYPERLINK.%20Such%20omission%20is%20irrelevant%20in%20determining%20whether%20the%20formula%20works%20because%20the%20friendly_name%20argument%20is%20optional%20anyway.%20Nonetheless%2C%20I%20have%20accordingly%20included%20the%20Column%20Letter%20%22K%22%20in%20the%20formula%20above%2C%20which%20I%20want%20you%20to%20manually%20type%2C%20rather%20than%20copy%20and%20paste.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-660653%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-660653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%20it%20worked%20perfectly%20when%20i%20typed%20it%20in%20manually.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20i%20can%20drag%20the%20formula%20down%20from%20row%20K1%20to%20K100%20such%26nbsp%3B%20that%20when%20i%20click%20on%20the%20link%20in%20sheet1%20K50%20it%20will%20take%20me%20to%20sheet2%20K50%3F%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-660835%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20dynamic%20hyperlink%20based%20on%20a%20changing%20cell%20reference%20in%20a%20different%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-660835%22%20slang%3D%22en-US%22%3EIn%20that%20case%2C%20the%20formula%20would%20be%20easier%20to%20construct%2C%20based%20on%20its%20relative%20row%2C%20like%20this%3A%3CBR%20%2F%3E%3DHYPERLINK(%E2%80%9C%23Hours!K%E2%80%9D%26amp%3BROW()%2C%3CBR%20%2F%3E%E2%80%9CHours!K%E2%80%9D%26amp%3BROW())%3C%2FLINGO-BODY%3E
BIBIE1440
New Contributor

Hello all, I am working in an excel workbook that has two different worksheets (sheet1 and sheet2). I am trying to create a hyperlink on sheet1 that can take me specific cells in sheet2. However, i am working with a lot of rows and i do not want to create a hyperlink for each row. Please does anyone know a formula that i can use once and able to drag so that it matches the respective cell reference? Thank you!

6 Replies
If you want to link to the last non-blank cell in Column A of Sheet2, you may create this formula in Sheet1:
=HYPERLINK(“#Sheet2!A”&
COUNTA(Sheet2!A:A),”Sheet2!”&
COUNTA(Sheet2!A:A))
Note that the name of the link in the foregoing formula is the address of the cell in Sheet2 to which the link will jump.

@Twifoo 

Thank you for your response. i get the attached message when i put in the formula.

This is the formula i typed in =HYPERLINK(“#Hours!K”&COUNTA(Hours!K:K),”Hours!”&COUNTA(Hours!K:K))

Thank you!

sheet2 is hours

K is the column in sheet2

I didn’t test the formula because I replied to you via mobile phone. I will correct the formula after I get some sleep.

The formula is correct! I surmise you copied and pasted my suggested formula, and then you modified it. Instead, please type this formula yourself: 

=HYPERLINK("#Hours!K"&COUNTA(Hours!K:K),
"Hours!K"&COUNTA(Hours!K:K))

I suspect the formula might have included some invisible characters when you copied and modified it. Also note that, in my originally suggested formula, I inadvertently omitted the Column Letter in the friendly_name argument of HYPERLINK. Such omission is irrelevant in determining whether the formula works because the friendly_name argument is optional anyway. Nonetheless, I have accordingly included the Column Letter "K" in the formula above, which I want you to manually type, rather than copy and paste. 

@Twifoo 

Thank you very much! it worked perfectly when i typed it in manually. 

Is there a way i can drag the formula down from row K1 to K100 such  that when i click on the link in sheet1 K50 it will take me to sheet2 K50?

Thank you!

 

Highlighted
In that case, the formula would be easier to construct, based on its relative row, like this:
=HYPERLINK(“#Hours!K”&ROW(),
“Hours!K”&ROW())
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies