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
Twifoo's avatar
Twifoo
Silver Contributor
May 30, 2019
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.
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!

         

Resources