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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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,402
How about:

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

?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,902
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top