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

Formulas and Functions

6 Replies

Sort By
  • 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.

Resources