data validation list to hyperlink help

DippNCope

Board Regular
Joined
May 21, 2009
Messages
77
I have a spread sheet that in sheet1 has a a data validation list to sheet2 of hyperlinks to documents. the list in sheet2 to has friendly names. example sheet to shows test.doc with a hyperlink to X:\folder\folder\folder\test.doc
The list is in A8 and in A9 I have
Code:
=HYPERLINK("#INDEX(hyperlinks,MATCH(A8,hyperlinks,0))","Click here to go to " & A8)
hyperlinks is named range
This finds the value selected in A8 and creates a hyperlink to matching value hyperlink on sheet 2. So I find what I want in list on A8 then I have to click on A9 hyperlink that takes me to sheet 2 where I have to click on hyperlink to open document.
Can someone point me to a way that will allow me to just click in a9 and have it open matching value hyperlink on sheet 2.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
Change the hyperlinks on sheet2 to be the actual hyperlink address instead of the friendly name, and remove the "#" from the formula you have, and it'll work.
 

DippNCope

Board Regular
Joined
May 21, 2009
Messages
77
Thanks, I ended up with 2 lists for hyperlinks 1 friendly name and the other the url and using this.


Code:
=HYPERLINK(IFERROR(INDEX(Bookmarks!$C$2:$C$97,MATCH(A8,Bookmarks!$A$2:$A$97,0)),""))
With A2:a97 being friendly name column and C2:c97 being url
validation list shows friendly names in A8 and url in A9
 

Turtle30

New Member
Joined
Jul 6, 2006
Messages
1
I tried a variation of this formula (Below) and when I click on the resulting "hyperlink", I get the error message "Cannot open the specified file"

=HYPERLINK(IFERROR(INDEX(Lists!$B$1:$B$49,MATCH(B1,Lists!$A$1:$A$49,0)),""))

The cell with data valiadation list is B1 in the first sheet and the hyperlinks are listed in a second sheet called "lists" in column B next to the Friendly names in column A. The hyperlinks lead to cells on the first sheet
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
How about:

=HYPERLINK(IFERROR("#"&INDEX(Lists!$B$1:$B$49,MATCH(B1,Lists!$A$1:$A$49,0)),""))

?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,645
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top