Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Dynamic Hyperlink Based on Cell value

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Hyperlink Based on Cell value

    Is there a way to create a hyperlink that is dynamically linked to a cell value, in which the cell value points to a cell reference within a worksheet, and that when the value is changed can point to a different cell reference.

    I am looking for a way to create a hyperlink without using Macros. I have a column (P) in a worksheet called Annual Review. Everything in column P is an automaticly generated Row number referencing the customers details on the other worksheet "MGP4" So if in P2 on "Annual Review" it says 43. I know that the Customers Details can be found on line 43 on worksheet "MGP4".

    Im looking for a way to make it a hyperlink but as a formula so i can copy it down, there are several thousand entries on both worksheets.

    If anyone can help me or has any questions i would be very grateful.

    Thanks,
    Harry

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Try:

    =HYPERLINK("#MGP4!A"&P2)

    You can add a second argument for the text to display if you want.
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    May 2010
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    That is outstanding! Thank you very much!!!

  4. #4
    Board Regular
    Join Date
    Aug 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Thank you. This was exactly what I wanted to know and your solution worked perfectly!

  5. #5
    Board Regular
    Join Date
    Feb 2006
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Quote Originally Posted by Andrew Poulsom View Post
    Try:

    =HYPERLINK("#MGP4!A"&P2)

    You can add a second argument for the text to display if you want.
    This works brilliantly. But how do you add a second arguement for the text to display?
    Dhira

  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Try

    =HYPERLINK("#MGP4!A"&P2,"Your text")
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular crook_101's Avatar
    Join Date
    Oct 2008
    Location
    Home: Sunny England Work: Rainy Scotland
    Posts
    682
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Hi,

    As Andrew is off-line, i have taken the liberty of responding

    This is what you are after??

    Cheers,
    Ian

    Sheet3

     B
    5nice name

    Spreadsheet Formulas
    CellFormula
    B5=HYPERLINK("#MGP4!A"&P2,"nice name")


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
    There is no such thing as a stupid question. Anon

    But, if you work for me and ask a really stupid question, I will either laugh heartily or you get a really stupid answer

  8. #8
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Thank You all for the answer and the question too

  9. #9
    Board Regular
    Join Date
    Apr 2015
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Hi

    Would this work with a formula embedded also?

    one would be an =offset and another is an =if formula.

    Thanks
    Richard

  10. #10
    New Member
    Join Date
    Feb 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Hyperlink Based on Cell value

    Quote Originally Posted by Andrew Poulsom View Post
    Try:

    =HYPERLINK("#MGP4!A"&P2)

    You can add a second argument for the text to display if you want.
    Question on the same line. I have two sheets. Column A of sheet 1 is having n entries and Column B of Sheet 2 is having same entries but not in the same order. I need to link those entries. How to do that?

    Thanks in advance.

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
  •