May 30 2019 12:05 PM
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!
May 30 2019 12:38 PM
May 30 2019 01:11 PM
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
May 30 2019 01:53 PM
May 30 2019 07:49 PM - edited May 30 2019 08:01 PM
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.
May 31 2019 05:22 AM
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!
May 31 2019 07:09 AM