MrExcel Publishing
Your One Stop for Excel Tips & Solutions

lookup hyperlinks


Posted by kristel on November 20, 2001 7:29 AM

i have a table, first column=keys , second column contains numbers but every number is also a hyperlink to a file.
instead of getting the value by using a simple vlookup, i want to know the hyperlink.
Has anybody an idea how i can solve thsi problem?
Thanks

Kristel


Posted by Aladin Akyurek on November 20, 2001 8:04 AM

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

Posted by kristel on November 23, 2001 7:36 AM

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

Posted by Aladin Akyurek on November 23, 2001 1:29 PM

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

Posted by kristel on November 26, 2001 1:00 AM

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

Posted by Aladin Akyurek on December 02, 2001 9:26 AM

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

Posted by kristel on December 03, 2001 7:26 AM

Bob Umlas


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