Hyperlink to cell in sheet based off of list of values
Results 1 to 7 of 7

Thread: Hyperlink to cell in sheet based off of list of values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2019
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hyperlink to cell in sheet based off of list of values

    Hi,

    I am tying to find a way to create a hyperlink that goes to a specific cell in my sheet. I want the hyperlink to be dynamic and change based off a data validation list of values.

    For example:

    Cell B2 contains my list of values, so in B3 I would like toinput a formula that says when B2=”Apple”, then hyperlink to cell D6, when B2=”Banana”,hyperlink to cell D57 and so on…

    Any ideas on how to achieve this?

    Thanks

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,063
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hyperlink to cell in sheet based off of list of values

    Quote Originally Posted by gaudrco View Post
    when B2=”Apple”, then hyperlink to cell D6, when B2=”Banana”,hyperlink to cell D57 and so on…
    Hi, how do we know that "apple" links to cell D6 and "banana" links to cell D57 - what is the logic? Are those values in the cells you want to link to?
    Last edited by FormR; Aug 16th, 2019 at 10:07 AM.
    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Aug 2019
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hyperlink to cell in sheet based off of list of values

    The point of what I am trying to accomplish is a simple way to navigate my spreadsheet.

    My spreadsheet has many rows so the logic is if I choose ‘Apples’in the dropdown list of values in cell B2, then cell B3 will dynamically changeto become a hyperlink that will direct me the cell in my spreadsheet that hasto do with Apples, which happens to be D6. Likewise, if I decide to change B2to “Bananas” then B3 will change to the hyperlink that directs me to the cellin my spreadsheet that has to do with Bananas, which happens to be D57.



    I tried a formula that looked like this but I got an errorthat said “Can’t open specified file”


    This formula is located in cell B3



    =IF($B$2="Apple",HYPERLINK(D6),IF($B$2="Banana",'HYPERLINK(D57)))


  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,063
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hyperlink to cell in sheet based off of list of values

    Hi, I understand what you are trying to do - I'm asking if there is a way to logically determine that apple links to cell D6 and bananas links to D57?

    If there is no logic, I'd suggest you create a list of the drop down values and the cell you want each to link to (cells E2:F4) in the example below and try like this:

    Excel 2013/2016
    BCDEF
    1DropdownLinkFruitLink Cell
    2appleClick here..AppleD6
    3BananaD57
    4PearD88

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=HYPERLINK("#"&VLOOKUP(B2,$E$2:$F$4,2,0),"Click here..")

    [code]your code[/code]

  5. #5
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,063
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hyperlink to cell in sheet based off of list of values

    If you really wanted to hard code the values and cell references in the formula, you could try like this.

    =HYPERLINK("#"&CHOOSE(MATCH(B2,{"apple","banana","pear"}),"D6","D57","D88"),"Click here..")
    [code]your code[/code]

  6. #6
    Board Regular
    Join Date
    Aug 2019
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hyperlink to cell in sheet based off of list of values

    I tried both methods and they both work! I prefer the hard coded method so that is what I went with. Thank you very much for you help!

  7. #7
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,063
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hyperlink to cell in sheet based off of list of values

    Quote Originally Posted by gaudrco View Post
    I prefer the hard coded method so that is what I went with.
    Great, although I missed the match_type parameter of the match() function - for robustness you should include the addition in red below:

    =HYPERLINK("#"&CHOOSE(MATCH(B2,{"apple","banana","pear"},0),"D6","D57","D88"),"Click here..")
    [code]your code[/code]

Some videos you may like

User Tag List

Tags for this Thread

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
  •