Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
If and hyperlinks....
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do long hyperlinks get truncated on save in Excel? | Excel Worksheet Functions | |||
Addressing hyperlinks in excel | Excel Discussion (Misc queries) | |||
Problems with hyperlinks | Excel Discussion (Misc queries) | |||
Can 2 different hyperlinks be in the same Excel cell? | Excel Worksheet Functions | |||
up-date hyperlinks in Excel | Excel Worksheet Functions |