Hyperlink Hyperlink - Go Away

Andy Gee · Mar 6th, 2002, 03:54 AM

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 · Mar 6th, 2002, 07:21 AM

Quote:

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 · Mar 6th, 2002, 07:37 AM

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 · Mar 6th, 2002, 07:47 AM

Okay, try this formula:

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

Regards,

Andy Gee · Mar 6th, 2002, 07:53 AM

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 · Mar 6th, 2002, 08:00 AM

Quote:

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...;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?

Mar 6th, 2002, 07:47 AM	#4
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	Okay, try this formula: =IF(ISBLANK(B1),"No link",(HYPERLINK(B1,A1))) Regards, __________________ Barrie Davidson "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 03:54 AM	#1
Andy Gee New Member Join Date: Feb 2002 Posts: 48	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

Mar 6th, 2002, 07:37 AM	#3
Andy Gee New Member Join Date: Feb 2002 Posts: 48	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 ]

Mar 6th, 2002, 07:53 AM	#5
Andy Gee New Member Join Date: Feb 2002 Posts: 48	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!