Excel help needed!!

[absthechatter]

Is there a formula I can input that will allow me to automatically add the last part of an email address to all the different names I put in front, and still let it be a hyperlink.

For example, If I want to put a different email address in each cell and they all end something like @absthechattershouse.com but the bit before the @ will always be different i.e. john.smith, david.green, peter.wilson etc.
I manged to get the end bit to be added each time, and it changes blue, but it does not create a hyperlink, is there anyway I can make it create one.

Many thanks

Abs

[gazzae]

How are you automatically adding the @myaddress.com bit?
__________________

Assuming your using concatenate you would do it like this...

=HYPERLINK(CONCATENATE(A3, $B$1))

[absthechatter]

Thanks gazzae


The "I" bit was really a bit of a royal we. In other words, someone on my team at work that has a clue what they are doing did this and got it to work but without a hyperlink - and then they cleared off home leaving me to send out the spreadsheet but without it doing what we want it to do. I now need to try and get it to work and get it sent tonight, so any help on how to do it would be most appreciated.

Many thanks

Abs

[gazzae]

if they did it using a formula all you need to do is put their formula in =hyperlink().

E.g

=HYPERLINK(CONCATENATE(A3, $B$1))

or

=HYPERLINK(SUM(C5:C14))

That will make the result of the formula a hyperlink.

[absthechatter]

Many thanks again gazzae

I now appear to have deleted whatever formula was there for getting the end bit in and now I have nothing!! Of course I decided to save what I have done and can no longer use the go back function

Any ideas what my formula may have been??

Many thanks from a stupid, fed up with sitting inside when it's sunny old git!

[gazzae]

It could have been the concatenate. For example. if the names are in column A then use the following formula

=HYPERLINK(CONCATENATE(A1, "@myaddress.com"))

[absthechatter]

Originally Posted by gazzae

It could have been the concatenate. For example. if the names are in column A then use the following formula

=HYPERLINK(CONCATENATE(A1, "@myaddress.com"))

Brilliant!!
That works perfectly for me.

Many thanks again

Abs

[gazzae]

Glad I could help.

[absthechatter]

Originally Posted by absthechatter

Brilliant!!
That works perfectly for me.

Many thanks again

Abs

Oooopss, I spoke too soon!!! It now shows as a hyperlink, but when I click on it, it says cannot open the specified file rather than open outlook and inputting the email address for me.

ASny ideas what I am doing wrong?

Abs

[gazzae]

Your right, it doesn't work. Its missing the mailto: bit of the hyperlink.

Add that in at the start...

=HYPERLINK(CONCATENATE("mailto:",A2, "@myaddress.com"), A2)

The , A2) at the end just makes the link look nicer as it doesn't show the mailto: bit.

[absthechatter]

Originally Posted by gazzae

Your right, it doesn't work. Its missing the mailto: bit of the hyperlink.

Add that in at the start...

=HYPERLINK(CONCATENATE("mailto:",A2, "@myaddress.com"), A2)

The , A2) at the end just makes the link look nicer as it doesn't show the mailto: bit.

Ah ha, that'll be the one.

Thanks for all your help again.

Abs