• 411K Members
• 4,893 Online
• 467K Conversations

New Contributor

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

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
Highlighted

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

If you want to link to the last non-blank cell in Column A of Sheet2, you may create this formula in Sheet1:
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.

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

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

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

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

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

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

"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.

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

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!

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

In that case, the formula would be easier to construct, based on its relative row, like this:
“Hours!K”&ROW())
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies