drop down list with hyperlink

Robert Edens · Mar 29, 2011

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

DippNCope · Mar 29, 2011

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

Robert Edens · Mar 30, 2011

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

DippNCope · Mar 30, 2011

Something here maybe?
http://www.excel-videos.com/excel-functions/excel-magic-trick-139-hyperlink-function-drop-down-list

Andrew Poulsom · Mar 30, 2011

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

DippNCope · Mar 30, 2011

instead of

Code:

[B]=HYPERLINK(A1, "Goto Link")[/B]

Try

Code:

=HYPERLINK("[YourFile.xlsx]YourSheet!A1", "Go To Link")

Robert Edens · Mar 30, 2011

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.

Mrutyunjay · Mar 19, 2014

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 !!!

nicopinsloo · Jul 18, 2015

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

drop down list with hyperlink

Robert Edens

New Member

Excel Facts

DippNCope

Board Regular

Robert Edens

New Member

DippNCope

Board Regular

Andrew Poulsom

MrExcel MVP

DippNCope

Board Regular

Robert Edens

New Member

Mrutyunjay

New Member

nicopinsloo

New Member

Similar threads

Forum statistics

Share this page

drop down list with hyperlink

New Member

Excel Facts

Board Regular

New Member

Board Regular

MrExcel MVP

Board Regular

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock