Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hyperlink w/ INDIRECT or similar?

    I have a list of sheet names that I want to be able to navigate easily. I'd like to add a formula into the cell next to each sheet name that will automatically generate a hyperlink based on the sheet name in the adjacent cell.

    psuedo-code:
    A5=goto INDIRECT(&B5&)!A1
    B5="SheetName1"

    I'd rather not have to manually create the hyperlinks as the list changes frequently. Is this possible?

    Also, looking at the manually created hyperlinks - can they omit the filename or at least the filepath? The speadsheets will not be used from the same relative or absolute path on a regular basis.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Use the hyperlink function:
    =HYPERLINK("[Filename.xls]"&B5&"!A1")

    You need the filename of an open workbook, the complete path of a closed workbook so it can be found and opened. The filename could be in a cell as well. It could be calculated for the activeworkbook extracting it from (it is in brackets):
    =cell("filename")

    For example if the link is in the activeworkbook:
    =HYPERLINK(MID(LEFT(CELL("filename"),FIND("]",CELL("filename"))),FIND("[",CELL("filename")),LEN(CELL("filename")))&B5&"!A1 ")

    Steve

Posting Permissions

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