Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: drop down list with hyperlink

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default drop down list with hyperlink

    I found this solution for "drop down list with hyperlink" but it did not work.

    Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:

    =HYPERLINK(A1, "Goto Link")

    The solution directly above provides exactly what I am looking for
    in the field where I write the formula, but it fails to hyperlink.
    I have created a drop down list and linked each one of them to a
    specific worksheet. When I select them individually they link to
    appropriate worksheet. But when I select them in the drop down
    list I receive the following error when I select the Hyperlink in
    cell B1 as directed above.

    "Cannot open the specified file"

    Any thoughts?

    Bob
    Last edited by Robert Edens; Mar 29th, 2011 at 03:58 PM.

  2. #2
    Board Regular DippNCope's Avatar
    Join Date
    May 2009
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: drop down list with hyperlink

    I use something like that, What I have is a validation list on sheet 1.
    on sheet 2 I have 2 columns. Column A contains friendly names Column B has url. example sheet 2 A2 has test.doc, sheet 2 B2 has hyperlink X:\folder\folder\folder\test.doc
    I select friendly name in drop down list located in A1 and hyperlink is displayed in B1
    I placed the below code in B1 of sheet 1
    Code:
    =HYPERLINK(IFERROR(INDEX(Sheet2!$B$2:$B$100,MATCH(A1,Sheet2!$A$2:$A$100,0)),""))
    *Edited spelling mistake in code

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: drop down list with hyperlink

    That did not work for me. Let me explain with more detail.

    Using Data Validation I create a drop down list in Cell A1 with data contained in cell A10:A11. I repeat this in Cell A2 same drop down list with data contained in cell A10:A11. I understand that in cells A1 and A2 the hyperlink does not replicate when selected from drop down list, but it is my understanding that a formula may be written in cell B1 and B2 for example that will capture the hyperlink in cells B1 & B2 respectively. I successfully wrote a formula that creates a hyperlink, but when selected receives error “Cannot open specified file.” The formula I wrote is “=HYPERLINK(A1, “Link”). Link is what is illustrated in Cell B1.

    Example:
    Cell A1 is Drop down List with data in Cell A10:A11 Cell B1 = Formula “=HYPERLINK(A1, “Link”)
    Cell A2 is Drop down List with data in Cell A10:A11 Cell B2 = Formula “=HYPERLINK(A2, “Link”)

    Cell A10 is Sheet 2 hyperlink
    Cell A11 is Sheet 3 hyperlink

  4. #4
    Board Regular DippNCope's Avatar
    Join Date
    May 2009
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: drop down list with hyperlink


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

    Default Re: drop down list with hyperlink

    Please give an example of what's in your dropdown list. Have you saved the workbook?
    Microsoft MVP - Excel

  6. #6
    Board Regular DippNCope's Avatar
    Join Date
    May 2009
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: drop down list with hyperlink

    instead of
    Code:
    =HYPERLINK(A1, "Goto Link")
    Try
    Code:
    =HYPERLINK("[YourFile.xlsx]YourSheet!A1", "Go To Link")

  7. #7
    New Member
    Join Date
    Mar 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: drop down list with hyperlink

    DippNCote,
    you have it real close. The idea is the following. My workbook is a proposal tool. It has 10 worksheets that may or may not become part of the proposal. In the worksheet named "Tbl" short for Table of Contents, I have 10 rows of drop downs using data validation. The data validation range is directly below the top 10 rows. After I complete the input for the proposed solution, then I go to the "Tbl" worksheet and depending on how many worksheets will be used in the proposal, let's say 5, I go to row one and select the worksheet from the drop down box, then row 2 and repeat, etc. until I have chosen the 5 unique worksheets for this proposal. In the "Tbl" Table of Contents worksheet I want to be able to link to the worksheets that I selected. I then will convert the excel into PDF and the links carry forward. Therefore, when a customer receives the PDF they may link from the Table of Contents to the particular section of the proposal they wish. I have created a home link from each worksheet that returns to Table of Contents page in the PDF. I will name the Hyperlink "Section 1", "Section 2", etc. So it shows on the "Tbl" worksheet like this.

    Cover Letter Section 1 note: Section 1 is the link to the selected worksheet.


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

    Lightbulb Re: drop down list with hyperlink

    Hi Bob,

    You can try below it did work for me
    if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:

    =HYPERLINK("Goto Link","A1")

    Example:
    I want to check for the condition , if i am having doubt while checking anything then i want to look in wikipedia. in my drop down list i have 1 note called doubt . So whenever i am having any doubt i will click on that and i will direct me to wikipedia .
    = HYPERLINK ("http://en.wikipedia.org/wiki/Main_Page ","doubt")

    Cheers !!!
    Last edited by Mrutyunjay; Mar 19th, 2014 at 04:54 AM.

  9. #9
    New Member
    Join Date
    May 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: drop down list with hyperlink

    Hyperlinks in sheet 1 a1:a10
    all 10 hyperlinks target sheet 2 a1
    if any hyperlink in sheet 1 are selected
    data of that cell to be written into sheet 2 a1
    sheet 1 a1 = aa then sheet 2 a1 = aa
    sheet 1 a2 = bb then sheet 2 a1 = bb
    and so on

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
  •