• 462K Members
• 5,453 Online
• 559K Conversations

Highlighted
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

# 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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies