data validation list to hyperlink help

[DippNCope]

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

=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.

 

[GlennUK]

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]

Thanks, I ended up with 2 lists for hyperlinks 1 friendly name and the other the url and using this.

=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

 

[GlennUK]

That's great!

 

[Turtle30]

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]

How about:

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

?