Hyperlink Hyperlink - Go Away

[Andy Gee]

Trigger happy hyperlinks

This hyperlinks if B1 is blank or not!
=IF(B1<>"",(HYPERLINK(B1,A1)),"No link")

This hyperlinks if B1 is text or not!
=IF(ISTEXT(B1),HYPERLINK(B1,A1),"No Link")

A1 = Mr Excel
B1 = http://www.mrexcel.com

Please help stop this renegade function before my head falls or explodes

 

[Barrie Davidson]

On 2002-03-06 02:54, Andy Gee wrote:
Trigger happy hyperlinks

This hyperlinks if B1 is blank or not!
=IF(B1<>"",(HYPERLINK(B1,A1)),"No link")

This hyperlinks if B1 is text or not!
=IF(ISTEXT(B1),HYPERLINK(B1,A1),"No Link")

A1 = Mr Excel
B1 = http://www.mrexcel.com

Please help stop this renegade function before my head falls or explodes

Andy, what are you trying to do?

 

[Andy Gee]

Sorry, I'll explain better, my head was a bit mashed from the circular direction I taking!

I am trying to create a hyperlink in cell C1 if B1 contains text (ie. a web address).
The Text for the new hyperlink should come from cell A1

If B1 is empty I would like C1 to show "No Link" and not be a hyperlink.

Example.
A1 = Mr Excel
B1 = http://www.mrexcel.com
C1 =IF(B1<>"",(HYPERLINK(B1,A1)),"No link")

This works but if B1 is empty it is still a hyperlink and causes an error when clicked

Please help!
This message was edited by Andy Gee on 2002-03-06 06:38

 

[Barrie Davidson]

Okay, try this formula:

=IF(ISBLANK(B1),"No link",(HYPERLINK(B1,A1)))

Regards,

 

[Andy Gee]

Try it, it still links to something! Some kids have to use this and they keep calling me when it reports "The address of this site is not valid... blah blah"

Please help!

 

[Barrie Davidson]

On 2002-03-06 06:53, Andy Gee wrote:
Try it, it still links to something! Some kids have to use this and they keep calling me when it reports "The address of this site is not valid... blah blah"

Please help!

You're absolutely correct and, sorry to be the bearer of bad news, nothing can be done to correct the problem. Microsoft acknowledges it is a problem, see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q156926

Sorry I couldn't help you out

PS - would a VBA solution work for you? I'm thinking of maybe putting in code in the worksheet that will insert a hyperlink as long as there is data in cells A1 and B1. What do you think?