Looking up hyperlinks

[Guest]

I have a formlua =VLOOKUP(a1,B1:c20,2,FALSE), where the cell being looked up
is a hyperlink. The formula return the correct path name for the hyperlink,
but the cell displayed is no longer a hyperlink. Anybody know how to retain
the hyperlink properties in these circumstances? Thanks

 

[Anne Troy]

Try this, Peter:
=Hyperlink(=VLOOKUP(a1,B1:c20,2,FALSE))
************
Anne Troy
www.OfficeArticles.com

 

[Guest]

Anne. I tried this without the 2nd =. While the result is a hyperlink, I get
the error message "can open the specified file" - although I know there's
nothing wrong with the original hyperlink. Any ideas? Thanks again.
--
Peter
London, UK

Try this, Peter:
=Hyperlink(=VLOOKUP(a1,B1:c20,2,FALSE))
************
Anne Troy
www.OfficeArticles.com

 

[Anne Troy]

Sorry! Pasted that second = sign. Getting cannot open... is not necessarily
a bad hyperlink being created. Do you get the same message if you type the
hyperlink literally? I just helped someone do something similar and it
worked like a charm...that's the only reason I ask.
************
Anne Troy
www.OfficeArticles.com

Anne. I tried this without the 2nd =. While the result is a hyperlink, I
get
the error message "can open the specified file" - although I know there's
nothing wrong with the original hyperlink. Any ideas? Thanks again.

 

[Guest]

Yes I've got it to work not thanks Anne. It's to do with how you set up the
hyperlink in the orginal cell being looked up. A straight path name works, so
does right click hyperlink, so does =hyperlink(xx), but "friendly names",
anywhere in the scenario don't. Many thanks for your help.

 

[Anne Troy]

You're quite welcome. So happy to help.
************
Anne Troy
www.OfficeArticles.com

 

[Dave Peterson]

The =vlookup() portion returns what's in the cell.

If the cell contains: www.microsoft.com

Maybe all you need to add is:
=hyperlink("http://" & vlookup(a1,b1:c20,2,false))

if it's an email address:
=hyperlink("mailto:" & vlookup(...))

Anne. I tried this without the 2nd =. While the result is a hyperlink, I get
the error message "can open the specified file" - although I know there's
nothing wrong with the original hyperlink. Any ideas? Thanks again.