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

[BIBIE1440]

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!

[#2]

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.

[#3]

@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

[#4]

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

[#5]

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. 

[#6]

@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!

[#7]

In that case, the formula would be easier to construct, based on its relative row, like this:
=HYPERLINK(“#Hours!K”&ROW(),
“Hours!K”&ROW())