lookup hyperlinks

Kristel --

If you use the table just for storage (that is, not for navigating), enter paths to relevant files as text. You can then create dynamically a hypelink to a desired file by:

=HYPERLINK(lookup-value,Table-of-Path-Values,2,0)

If you insist on being able to navigate from your table also, use the URL to the file directly instead of a friendly name. Again you can use:

=HYPERLINK(lookup-value,Table-of-Hyperlinks,2,0)

where each entry is an active URL instead of a "friendly name." This formula produces a clickable hyperlink.

Aladin

Thanks aladin but...
This concerns an existing table and other people need to get the friendly names out of it by using the VLOOKUP, it's just one person who needs the hyperlinks...I guess it won't be possible then???

Just tested the scenario which appears to work.

Construct a 3-column table of hyperlinks.

Fill in the 2nd column clickable hyperlinks with friendly names.
Fill in the 3rd column either with paths paths to relevant files as text or with clickable URLs (without friendly names). You can hide the 3rd column. Then use:

=HYPERLINK(VLOOKUP(lookup-value,3-column-table-of-hyperlinks,3,0),VLOOKUP(lookup-value,3-column-table-of-hyperlinks,2,0))

This gives a clickable hyperlink with a friendly name.

A bit convoluted, but it appears to work.

Aladin

thanks for your help, Aladin!
We're almost where i wanted to get...
if it could be possible to fill in this 3th column automatically using some kind of function that could return the path using the link in the second column...
Someone has to change the hyperlinks now and then,you see, and then they wil surely forget to adapt the 3th column too...

What follows will not work on the Mac (I don't why).

You need to add the following UDF to your workbook:

Function WhatsURL(Rg As Range)
'
' Bob Umlas
'
WhatsURL = Rg.Hyperlinks(1).Address
End Function

To add it to your workbook,

activate Tools|Macro|Visual Basic Editor;
activate Insert|Module;
copy the UDF above and paste it in the pane entitled "...(code)".
activate File|Close and Return to MS Excel.

Now enter in the first cell of th 3rd column

=WhatsUrl(the-cell-address-of-the-first-hyperlink)

and copy down this as far as needed.

Use the last formula that I propsed, that is:

=HYPERLINK(VLOOKUP(lookup-value,3-column-table-of-hyperlinks,3,0),VLOOKUP(lookup-value,3-column-table-of-hyperlinks,2,0))

Aladin

Bob Umlas

YOU'RE GREAT !!!!
Thanks a lot!