I have a hyperlink in a cell, which i need to delete based on another event....

I was hoping to do this using an IF function, or maybe conditional
formatting, but cannot work it out....

Ill show what i mean because this can be confusing


In say, cell A1, i have a hyperlink, called abc. It actually links to a
webpage say, http://www.abc.co.uk. In cell B1 i have a number, which changes
between 0 and 1

when the number 0 appears, i want the hyperlink to stay, but when it changes
to a 1 i want the hyperlink to disappear. This is because of a macro I am
trying to run...


The problem is, because the actual web page is not in the cell, when i try
to do an IF function,in cell A1, i may do =IF(B1=0, abc). I leave the false
part blank so it returns a 0. This doesnt work though, as the abc part
remains the same hyperlink, but when a 0 is returned when false, this too
stays as a hyperlink to exactly the same web page.....but i need it to be
gone completely, or at least not a hyperlink.

I have tried ways to get around this, but i cannot change the format of the
link, ie. change it to www.abc.co.uk, rather than just abc, as the macro I am
using relies heavily on this.


Thnx in advance for the help,
frazer

Can anyone help?

Frazer,

I think this is what you're after:

if you have the URLs in A1 going down, and the 0/1 value in b1 going
down, in c1 for your result, type: =IF(b1=0,a1,"")

so that is just saying if b1=0 then display the contents of a1, if its
anything else display nothing, which is the "". you coould put anything
in between the "" to be displayed, such as "not here"

hope that's what you needed...


--
saybut
------------------------------------------------------------------------
saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949
View this thread: http://www.excelforum.com/showthread...hreadid=396766

Hmm yea i tried that but there are several problems:

Firstly, if you have in cell c1 =if(b1=0, a1, ""), then when the value is 0,
it copies the hyperlink, but only the text part of the hyperlink, i.e. if the
cell says abc which is a hyperlink to www.abc.com or whatever, then when the
cell is copied across via the formula then the cell just says abc, and isnt
hyperlinked....

I can do in cell d1 then =hyperlink(c1), but this is also uselss as it just
makes the abc hyperlinked, but to nothing.....

"saybut" wrote:


Frazer,

I think this is what you're after:

if you have the URLs in A1 going down, and the 0/1 value in b1 going
down, in c1 for your result, type: =IF(b1=0,a1,"")

so that is just saying if b1=0 then display the contents of a1, if its
anything else display nothing, which is the "". you coould put anything
in between the "" to be displayed, such as "not here"

hope that's what you needed...


--
saybut
------------------------------------------------------------------------
saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949
View this thread: http://www.excelforum.com/showthread...hreadid=396766

Ah right, I've just tried it then with the =hyperlink, it seems to work,
just you need to have the http://, not just www., I tried a couple
http://news.bbc.co.uk etc and http://www.abc.co.uk and they seem to
work fine.


--
saybut
------------------------------------------------------------------------
saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949
View this thread: http://www.excelforum.com/showthread...hreadid=396766

How about this in A1:

=HYPERLINK(IF(b1=1,"http://www.microsoft.com","#"&CELL("address",A1)),
"click me")

(One cell)

If b1=1, then you get to go to the site. If b1=0, then you link to that same
cell (A1).



Frazer wrote:

I have a hyperlink in a cell, which i need to delete based on another event....

I was hoping to do this using an IF function, or maybe conditional
formatting, but cannot work it out....

Ill show what i mean because this can be confusing

In say, cell A1, i have a hyperlink, called abc. It actually links to a
webpage say, http://www.abc.co.uk. In cell B1 i have a number, which changes
between 0 and 1

when the number 0 appears, i want the hyperlink to stay, but when it changes
to a 1 i want the hyperlink to disappear. This is because of a macro I am
trying to run...

The problem is, because the actual web page is not in the cell, when i try
to do an IF function,in cell A1, i may do =IF(B1=0, abc). I leave the false
part blank so it returns a 0. This doesnt work though, as the abc part
remains the same hyperlink, but when a 0 is returned when false, this too
stays as a hyperlink to exactly the same web page.....but i need it to be
gone completely, or at least not a hyperlink.

I have tried ways to get around this, but i cannot change the format of the
link, ie. change it to www.abc.co.uk, rather than just abc, as the macro I am
using relies heavily on this.

Thnx in advance for the help,
frazer

Can anyone help?

--

Dave Peterson