• 518K Members
• 6,674 Online
• 616K 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

# 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.
Highlighted

# 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
Creating A Sublist
zjohnson in Excel on
5 Replies
Cannot concatenate a hyperlink from another cell
h-i-k in Excel on
4 Replies
not able to type number and text in same cell?
Deleted in Excel on
2 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies