VLOOKUP - Hyperlinks Vanish

[Paul Cook]

Hello!

I am using VLOOKUP to bring matching data into the relevant field and while it works perfectly bringing the correct text across i lose the hyperlink.

Is there anyway to do make sure the full hyperlink is brought over?

 

[Gary''s Student]

There are two parts to a hyperlink:

1. the URL
2. the "friendly name"

The first thing to do is to make sure the two items match, so you see the
URL if you view the cell. Once that is done replace:

=VLOOKUP()
with:
=HYPERLINK(VLOOKUP())

to produce a nice, "clickable" link

 

[Paul Cook]

Actually thinking about it i dont need to use the VLOOKUP Function.



I am using a Advanced filter which copies into another sheet etc...

Againt the text comes over and looks like a link (blue/underlined) but isnt a link, so is there a way to fix that?

 

[Mike H]

Paul,

Lets say we have a list of web sites and their full addresses in sheet 2 in
columns A & B. Here's and example of that data for Google

Col A Col B
Google http://www.google.co.uk/


Try this in sheet 1

=HYPERLINK(VLOOKUP(A2,Sheet2!A1:B30,2,FALSE),A2)

Where A2 is the site to lookup (Google)

Mike

 

[Dave Peterson]

Just to add...

Formulas bring back values. They don't bring back hyperlinks or formatting or
comments or...

 

[Shane Devenshire]

Hi,

i see you never got an answer to your revised question. The answer is not.
Advanced filter never brings hyperlinks across, but as you found out it does
bring formatting. Instead use Data, Filter AutoFilter, highlight the
resulting cells and copy them, move to another sheet and paste them, the
hyperlinks will come along.

If you are using advanced filter because it allows fancier criteria, use it
but choose the Filter in place option rather than copying to another
location, then again choose the filtered data and manually copy and paste it
to a new location.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire