Lookup returns hyperlinked values as normal text

[Guest]

Hi,

I am using VLookups to populate several workbooks with data from a master
workbook. My current problem is that on the master book, there are e-mail
addresses which are currently hyperlinked. When I use the VLookup to
populate my cells with e-mail addresses, is there any way that I can keep the
hyperlinked properties of the data?

Many thanks,

Karen

 

[Dave Peterson]

Formulas won't return the links, but depending on what you're returning, maybe
you could use that to build the hyperlink:

=hyperlink(vlookup(...))
or
=hyperlink("mailto:" & vlookup(...))

But that value returned has to be the link--not just a nice displayed
name/string.

 

[Guest]

Hi Dave,

This looks good. Yes, it is the link and the mailto suggestion looks great.

Thanks very much again.

Take care,

Karen

 

[Guest]

Hi Dave,

This is the formula that is returning the non-hyperlink value

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",VLOOKUP($A6,applicant,14,FALSE)))

Now I'm going to try and insert your hyperlink functions

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",hyperlink("mailto:" & VLOOKUP($A6,applicant,14,FALSE))))

What do you think? Am I getting close?

This gives me the full mailto:emailaddress result, but is not hyperlinking.
I think I must be missing something.

Any ideas?

Thanks Dave,

Karen.

 

[Dave Peterson]

Are you sure it's not a hyperlink?

When I did this, it didn't look pretty, but I could click on that cell and an
email message would be started.

If the looks are important, you could format the cell:
select the cell/range
format|Style|hyperlink
(from the top dropdown list)

 

[Guest]

Hi Dave,

Thanks for your reply.

Not sure if my first reply to this went, so apologies if this is the second
time I've replied here.

When I hover over the cell, the mouse pointer becomes the gloved hand, but
when I click the cell it doesn't go anywhere. Not even if I press Control
and click. Normal hyperlinks work. Have I got my formula correct? It is
below:

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",hyperlink("mailto:"&VLOOKUP($A6,applicant,14,FALSE))))

As for how it looks, I don't mind how pretty it is, as long as it works!

Any further ideas?

Thanks Dave.

Karen.

 

[Dave Peterson]

It worked ok for me.

What do you see in the cell--not the formula, but the value?

I saw:
mailto:[email protected]

(or something like that.)

Hi Dave,

Thanks for your reply.

Not sure if my first reply to this went, so apologies if this is the second
time I've replied here.

When I hover over the cell, the mouse pointer becomes the gloved hand, but
when I click the cell it doesn't go anywhere. Not even if I press Control
and click. Normal hyperlinks work. Have I got my formula correct? It is
below:

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",hyperlink("mailto:"&VLOOKUP($A6,applicant,14,FALSE))))

As for how it looks, I don't mind how pretty it is, as long as it works!

Any further ideas?

Thanks Dave.

Karen.

 

[Guest]

Hi Dave,

Yes, using your example, I saw that too.

The funny thing is that if I copy and paste special the value returned by
the formula, this is clickable and starts a new addressed e-mail message.
However, the formula result still does not!

Odd but true. I'll take a look around and see if I have any capabilities
switched off.

Thanks for your help Dave.

Take care,

Karen.

 

[Dave Peterson]

Maybe there's a difference in versions.

I use xl2003.

Hi Dave,

Yes, using your example, I saw that too.

The funny thing is that if I copy and paste special the value returned by
the formula, this is clickable and starts a new addressed e-mail message.
However, the formula result still does not!

Odd but true. I'll take a look around and see if I have any capabilities
switched off.

Thanks for your help Dave.

Take care,

Karen.

 

[Guest]

Hi Dave,

Yes, I too use XL2003.

A bit odd - huh?

I'll try it in a different spreadsheet and see if anything happens then.

Take care and thanks again.

Karen.

 

[Guest]

Hi Dave,

Sorry to trouble you again. I've noticed that when using the hyperlink
function Excel likes the text forming the link to be in quotation marks.
However, my text will not be in quotation marks as the result of the formula.
Do you know of any way in which I can have the hyperlink bit of my formula
be displayed in quotation marks?

Here's my formula again.

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",hyperlink("mailto:"&VLOOKUP($A6,applicant,14,FALSE))))

I'll also post this up as a separate hyperlink query, and see if anyone else
has any ideas.

Thanks Dave,

Karen

Hi Dave,

Yes, I too use XL2003.

A bit odd - huh?

I'll try it in a different spreadsheet and see if anything happens then.

Take care and thanks again.

Karen.

 

[Dave Peterson]

It's not the =hyperlink() function that likes the double quotes--it's just the
way literal strings in excel are used.

If you had this in A1:
mailto:[email protected]

You could use:
=HYPERLINK(A1)

And get that hyperlink.

In your case, you don't have mailto: in each of the cells. So you have to
prefix "mailto:" to that string.

And to concatenate that string, you have to surround it with double quotes--just
like:

="Please pay by: " & text(a1,"mm/dd/yyyy")

If you meant you wanted to display the email address without the mailto:, you,
er, well, at least I could use something like:

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",
HYPERLINK("mailto:"&VLOOKUP($A6,applicant,14,FALSE),
VLOOKUP($A6,applicant,14,FALSE))))

Just one more thought about the hyperlink...

If you see that index pointer, then it sure looks like excel is creating a
hyperlink ok.

Maybe it's the email program (or excel and the email program) that isn't
working.

If you try:
=hyperlink("mailto:[email protected]")
does that start a new composition in your email program?

 

[Guest]

Hi Dave,

Thanks for your reply.

I don't mind seeing the mailto: - no problems.

In answer to

If you try:

=hyperlink("mailto:[email protected]")
does that start a new composition in your email program?

Yes, it does! That's why I thought I needed the "". I should have realised
that the "" were for to sting the text.

I think this is really odd. I'm wondering if I may have any formats that
are obstructing the hyperlink function. Who knows - I'm completely stumped!

Thanks again for your help.

Take care,

Karen

 

[Guest]

Have read previous posts and what you described to Karen is exactly my
situation however when I click after making it a hyperlink I get a "Can't
open the specified file" instead of email block. Suggestions?

 

[Dave Peterson]

Maybe you could share what your formula looks like--and if it includes other
cells, what each portion of the formula evaluates to.

Have read previous posts and what you described to Karen is exactly my
situation however when I click after making it a hyperlink I get a "Can't
open the specified file" instead of email block. Suggestions?

 

[wally_sa]

Dave, i have also tried all that was discussed below and have the exact same
problem as Steve.

let me briefly explain what I am trying to do:

I have a sheet (in the workbook) that you could vall the "database". In this
sheet of the workbook I have a number of colums with data that is populated
in there. On a second sheet (of the smae workbook) I have a "summary sheet"
that takes all the info from the "database" and poplulates the summary sheet
with the info by using vlookups.

One of the columns is also a hyperlink from the "database" to a external
document outside the current workbook. And now I also can't get the vlookup
to work for the mentioned hyperlink(s).

The formula in the summary sheet are as follows:

=IF($M$3="","",VLOOKUP($M$3,ORIG!$A$4:$BK$1001,59,FALSE))

where $M$3 is the value in the summary sheet that is to be looked up from
the "database", and the vlookup statement are all the values that are carried
over from the database to the summary sheet.

The formula is for the cell in the summary sheet that I want to have
populated with the hyperlink from the "database".

Please help!!!

Wally Anderson

 

[Dave Peterson]

Formulas won't return the hyperlink.

But if the value that's returned looks like a link, you could use another cell:

If A1 returns the value that looks like a link:
=hyperlink(a1)
or
=hyperlink("http://" & a1)
or
=hyperlink("mailto:" & a1)
or ...

Dave, i have also tried all that was discussed below and have the exact same
problem as Steve.

let me briefly explain what I am trying to do:

I have a sheet (in the workbook) that you could vall the "database". In this
sheet of the workbook I have a number of colums with data that is populated
in there. On a second sheet (of the smae workbook) I have a "summary sheet"
that takes all the info from the "database" and poplulates the summary sheet
with the info by using vlookups.

One of the columns is also a hyperlink from the "database" to a external
document outside the current workbook. And now I also can't get the vlookup
to work for the mentioned hyperlink(s).

The formula in the summary sheet are as follows:

=IF($M$3="","",VLOOKUP($M$3,ORIG!$A$4:$BK$1001,59,FALSE))

where $M$3 is the value in the summary sheet that is to be looked up from
the "database", and the vlookup statement are all the values that are carried
over from the database to the summary sheet.

The formula is for the cell in the summary sheet that I want to have
populated with the hyperlink from the "database".

Please help!!!

Wally Anderson