Forum Discussion

BIBIE1440's avatar
BIBIE1440
Copper Contributor
May 30, 2019

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 a...
Formulas and Functions
BIBIE1440's avatar
BIBIE1440
Copper Contributor
May 30, 2019

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

Twifoo's avatar
Twifoo
Silver Contributor
May 30, 2019
I didn’t test the formula because I replied to you via mobile phone. I will correct the formula after I get some sleep.
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 31, 2019

    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. 

    • BIBIE1440's avatar
      BIBIE1440
      Copper Contributor
      May 31, 2019

      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!

       

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        May 31, 2019
        In that case, the formula would be easier to construct, based on its relative row, like this:
        =HYPERLINK(“#Hours!K”&ROW(),
        “Hours!K”&ROW())

Resources