Hyperlinks in VLOOKUP
Posted by Simon James-Morse on August 13, 2001 4:26 AM
I have a table of stock items, where one field is a hyperlink to a Word file containing a detailed specification of each line item. However, when I use VLOOKUP to retrieve the data, only the 'friendly' text appears, ie the hyperlink capability is lost.
Does anyone know how can I retain the hyperlink?
Simon James-Morse
Posted by Aladin Akyurek on August 13, 2001 5:52 AM
Care to post the VLOOKUP formula?
Aladin
Posted by Simon James-Morse on August 13, 2001 6:15 AM
Its the basic formula, ie
=VLOOKUP(1,Rawdata,2)
where Rawdata is the range name of the data table
Simon
Posted by Aladin Akyurek on August 13, 2001 7:27 AM
Simon,
Can we carry out a little experiment?
Make the column with hyoerlinks the last column of your table and name RawData the range that excludes that column of hyperlinks. I'm proposing this because it sounds as if when VLOOKUP oes its job, the hyperlinks column undergoes a change. (By the way, I never heard of this prolem before.)
Aladin
Posted by Simon James-Morse on August 13, 2001 7:45 AM
The hyperlink field(s) in the RawData area are fine, i.e. they remain click-able links to the designated file. This is the same when the named RANGE includes or excludes the the column of hyperlinks.
But when VLOOKUP retrieves the selection, the hyperlink "hotspot" is lost. All you get is what is displayed in the hyperlink column.
Simon Simon, Can we carry out a little experiment? Make the column with hyoerlinks the last column of your table and name RawData the range that excludes that column of hyperlinks. I'm proposing this because it sounds as if when VLOOKUP oes its job, the hyperlinks column undergoes a change. (By the way, I never heard of this prolem before.)
Posted by Aladin Akyurek on August 13, 2001 8:30 AM
> The hyperlink field(s) in the RawData area are fine, i.e. they remain click-able links to the designated file. This is the same when the named RANGE includes or excludes the the column of hyperlinks.
> But when VLOOKUP retrieves the selection, the hyperlink "hotspot" is lost. All you get is what is displayed in the hyperlink column.
I think I now understand what the issue is. You're looking up with VLOOKUP a hyperlink & and the retrieved hyperlink is not clickable. Right?
I suggest that you only record just the (non-clickable) URL's (e.g., 26693.html ) in your hyperlinks column in the RowData range. And use the following formula when you do a lookup for URL's:
=HYPERLINK(VLOOKUP(lookup-value,RawData,column-URLs,0),"Click Here)
This formula will make the retrieved URL clickable.
Aladin
Posted by Simon James-Morse on August 13, 2001 8:56 AM
That's it! Excellent!!
You can actually place *two* VLOOKUP commands within the HYPERLINK command; one for the click-able URL, and one for the "Click Here" equivalent.
My sincere thanks
Simon > The hyperlink field(s) in the RawData area are fine, i.e. they remain click-able links to the designated file. This is the same when the named RANGE includes or excludes the the column of hyperlinks. > But when VLOOKUP retrieves the selection, the hyperlink "hotspot" is lost. All you get is what is displayed in the hyperlink column. I think I now understand what the issue is. You're looking up with VLOOKUP a hyperlink & and the retrieved hyperlink is not clickable. Right? I suggest that you only record just the (non-clickable) URL's (e.g., 26693.html ) in your hyperlinks column in the RowData range. And use the following formula when you do a lookup for URL's: =HYPERLINK(VLOOKUP(lookup-value,RawData,column-URLs,0),"Click Here) This formula will make the retrieved URL clickable.
