Hyperlink in nested formula not linking

[Guest]

Hi,

I'm using a VLookup to insert an email address into a spreadsheet, and I
would like the hyperlink to work.

This is my formula

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

This gives me the mailto:emailaddress but it is not clickable. However, if
I then paste (special) the resulting values of the formula to another cell it
works.

The original cell with the e-mail address in (in the vlookup table) links
fine.

I have noticed that when using the Hyperlink function (not nested), the
address needs to be in quotation marks. Could this be my problem here? If
so, is there anything I can add to my formula which would enclose the
hyperlink bit in quotation marks and make it work?

Any ideas?

Many thanks,

Karen.

 

[Dave Peterson]

One more question...

Do you have an existing hyperlink (insert|Hyperlink) in that cell with the
formula?

 

[Guest]

Hi Dave,

I thought I might have, but have just retyped the formula in a completely
blank cell and the hand still didn't prepare me an e-mail!

Thanks for the thought though.

Take care,

Karen

 

[Dave Peterson]

I'm pretty much out of guesses.

Maybe someone who's using xl2003 can try it and see if it works for them.

One more test for you...

If you build a new workbook with just enough data to make it a valid test, can
you create the same formula and see if it works in that workbook?

 

[Guest]

Hi Dave,

yes, thanks I'll try this. On a bizarre note, I had my laptop out with me
today and was demonstrating how this file works. I clicked the e-mail
address (from the formula) and it worked! Come back home, plug in to
Broadband, try the link. No go! Yet still any basic hyperlink function
e-mails and insert hyperlinks work. Now that's strange to me.

I'll let you know how I get on with the test spreadsheet.

Thanks a lot.

Karen.

 

[Dave Peterson]

Insert theme from the Twilight Zone here.

Hi Dave,

yes, thanks I'll try this. On a bizarre note, I had my laptop out with me
today and was demonstrating how this file works. I clicked the e-mail
address (from the formula) and it worked! Come back home, plug in to
Broadband, try the link. No go! Yet still any basic hyperlink function
e-mails and insert hyperlinks work. Now that's strange to me.

I'll let you know how I get on with the test spreadsheet.

Thanks a lot.

Karen.

 

[Guest]

Lol. You got it right there!

Thanks for all your help with this one Dave. If it ever works, I'll let you
know.

Take care and thanks again.

Karen.

 

[Dave Peterson]

Good luck.

Lol. You got it right there!

Thanks for all your help with this one Dave. If it ever works, I'll let you
know.

Take care and thanks again.

Karen.