Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: =HYPERLINK()

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Williston ND
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What a nice site you have here! I've been trying to make a HYPERLINK type formula that once made can be dragged to 600 other cells to create hyperlinks.
    (Example)
    I have column of numbers at C2:C9 on sheet2 & a column of the same numbers in differing orders on sheet1 C2:C9, on sheet2,
    In D2 ( and below) I would like a hyperlink to take me to the row on sheet1 that has the number reffered to on sheet2. Like this
    In E2(sheet2)
    =MATCH(C2,sheet1!$C$2:$C$9,0) which returns "5"
    In F2 (sheet2)
    =ADDRESS(E2+1,3,3,1,"sheet1") which
    returns sheet1!$C6
    sheet1!$C6 is right and this is where the link should point me to.
    I've tried alot of different formula's and combo's of formula's, here are a couple
    In sheet2 D2 I wrote:

    =HYPERLINK(F2,"go there") or

    =HYPERLINK(CELL("contents",F2),"go there")

    Only to see
    The link and all is there but doesn't go to that cell on that sheet.
    I think I am close but I am not sure.
    Of course this is just an example but I don't want to have to insert hyperlink by hand 600 times for the real workbook.

    Thank You in advance Paul

    [ This Message was edited by: polly on 2002-04-06 18:19 ]

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Polly:
    in your formulation

    =ADDRESS(E2+1,3,3,1,"sheet1") which
    returns sheet1!$C6

    for the hyperlink to work correctly,
    include the Workbook name

    so your formula would essentially become
    ="[Book1]"&ADDRESS(E2+1,3,3,1,"sheet1")
    and return
    [Book1]Sheet1!C6

    HTH

    Please post back if it works for you otherwise explain a liitle further ... and let us take it from there!





    _________________
    Yogi Anand
    Edit: Deleted inactive web site reference from hard code signature line

    [ This Message was edited by: Yogi Anand on 2003-01-19 17:21 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Williston ND
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi Anand,
    Works great. I put the book name in with the &(ampersand).
    ="[Book1.xls]"&ADDRESS(E2+1,3,3,1,"sheet1")

    I was starting to think Excel 97 wouldn't do it, every time I think that I find out it is me that couldn't figure it out.
    I was close to that at one time but the (&) was the key.

    Thank you so very very much
    Paul
    !!!!!this is a very wonderful site!!!!!

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Job Polly!
    Now time to get some rest ... and let us start the fun again tomorrow morn!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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