Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Lebanon, Illinois, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink to other sheets same workbook (2002)

    I have a tally sheet that has 2400 rows referencing 20 different rows on each of 120 sheets. I have macros to sort on 3 different columns. I need to be able to click a hyperlink to take the user to the sheet (named Tab1 through Tab120) that is referenced by given row. Inserting a 2400 hyperlinks isn't my idea of fun. Using the hyperlink formula requires the workbook name to be specified, but this is essentially a template and could be named anything and the hyperlink still needs to work.
    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to other sheets same workbook (2002)

    You can use the CELL function to retrieve the name of the filename and sheet, and use this in the HYPERLINK function:
    <code>
    =HYPERLINK(MID(CELL("filename",Tab1!A1),SEARCH("[",CELL("filename",Tab1!A1)),100)&"!A1")
    </code>
    This will provide a hyperlink to Tab1!A1 in the current workbook, regardless of the name under which it is saved. You can combine this with INDIRECT to let the sheet name depend on the row of the cell containing the formula:
    <code>
    =HYPERLINK(MID(CELL("filename",INDIRECT("Tab"&ROW( )&"!A1")),SEARCH("[",CELL("filename",INDIRECT("Tab"&ROW()&"!A1"))),10 0)&"!A1")
    </code>
    Are you still there? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Lebanon, Illinois, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to other sheets same workbook (2002)

    I'll give it a try - thank you so much.
    HoBob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •