Email hyperlink

[Tazzy via OfficeKB.com]

Hi all,

I have a work book that contains contact names and email addresses on one
sheet. Clicking on the cell containing the email address will then open a new
email addressed to that person.

However on a number of other sheets, I have the below formula;

=IF(AND(C22=""),"",HYPERLINK(VLOOKUP(C22,Students!$A$3:$D$39,4)))

C22 contains the contact name and the lookup automatically enters the email
address for that person. My problem is that that I cannot then click on that
email address and have a new message begin, I just get the error message
'Cannot open the specified file'

Right-clicking on the cell in this sheet does not give me the option of
editing the hyperlink, whereas it does on the main sheet containing the
details.

Anyone please help with this?

Thanks,

Tazzy

 

[Dave Peterson]

First, since you're matching by name, I would think you'd want to specify 0 or
False as the 4th parm in the =vlookup() portion. That means that you want an
exact match.

Second, depending on what's in column D, you may need something like:

=IF(C22="","",HYPERLINK("mailto:" & VLOOKUP(C22,Students!$A$3:$D$39,4,0)))

Third, there's no reason to use the =and() function in this formula.

 

[Tazzy via OfficeKB.com]

Hi Dave,

Thanks for the quick response, I'll be trying this out when I get a few
moments to myself and post back to let you know.

The =and() wasn't fully explained by me, purely because I've set the sheet up
for use by others not used to working with Excel, and it displayed the #
error messages otherwise which sort of scarede them into thinking they had
done something wrong.

Tazzy

 

[Tazzy via OfficeKB.com]

Hi Dave,

Just tried what you suggested and it wortks out fine - thanks.

Using the full formula you suggested confirms that you rightly said that I
did not need the =and(), so one for me to remember.

It's just slightly annoying that the lookup doesn't return the email
hyperlink exactly as it does on the original sheet thoug. Any idea why?

Tazzy

 

[Dave Peterson]

Formulas return values--not formatting, not hyperlinks, not formulas.

I guess you're lucky that the values are really email addresses <vbg>.