Hi
I have a complicated nested if formula that's being used to conditionally take the contents of 4 cells on a row and depending what is or isn't in the cells and put the date into one cell with some additional html formatting.
I will explain as I go along if I may...
This is what it looks like with all for website address cells filled in and the formula converted to its value (so I could show what the formula actually produced.
The cells with web addresses are filled out from left to right, if you only have one website you put that in Cell a1 (leaving the others blank), if you have two websites then you put one address in Cell a1 and the next in B1 and so on.
The formula is supposed to look at the row and if there is only one website added it then adds the text " onto the end of the site address and return the site address+" while ignoring the empty spaces.
If there are two addresses it adds the " on the end on the 2nd address only, if there are three addresses it adds the " on the end of the third and so on.
That's what I'd like it to do but what its actually doing is, if there is only one website (in Cell a1), its adding "" four times instead of just once.
If there are two websites (one in Cell a1 and the other in Cell b1) its adding "" three times
And so on.
I can not figure out where I have gone wrong so I am hoping a fresh pair of eyes may help.
This is my formula (its rather long) due to the HTML code it has to add to either side of the websites...
Thanks everyone, I know this is a big ask
edit: I have tried to add the results that formula produced but this site wont let me because I'm guessing it contains html code
I have a complicated nested if formula that's being used to conditionally take the contents of 4 cells on a row and depending what is or isn't in the cells and put the date into one cell with some additional html formatting.
I will explain as I go along if I may...
This is what it looks like with all for website address cells filled in and the formula converted to its value (so I could show what the formula actually produced.
The cells with web addresses are filled out from left to right, if you only have one website you put that in Cell a1 (leaving the others blank), if you have two websites then you put one address in Cell a1 and the next in B1 and so on.
The formula is supposed to look at the row and if there is only one website added it then adds the text " onto the end of the site address and return the site address+" while ignoring the empty spaces.
If there are two addresses it adds the " on the end on the 2nd address only, if there are three addresses it adds the " on the end of the third and so on.
That's what I'd like it to do but what its actually doing is, if there is only one website (in Cell a1), its adding "" four times instead of just once.
If there are two websites (one in Cell a1 and the other in Cell b1) its adding "" three times
And so on.
I can not figure out where I have gone wrong so I am hoping a fresh pair of eyes may help.
This is my formula (its rather long) due to the HTML code it has to add to either side of the websites...
Code:
=IF(A3<>"","<td><a href="&CHAR(34)&A3&CHAR(34)&" target="&CHAR(34)&"_blank"&CHAR(34)&">Click Here To View Website</a>","")&IF(B3="","</td>","")&IF(B3<>"","<a href="&CHAR(34)&B3&CHAR(34)&" target="&CHAR(34)&"_blank"&CHAR(34)&">Click Here To View Website</a>","")&IF(C3="","</td>","")&IF(C3<>"","<a href="&CHAR(34)&C3&CHAR(34)&" target="&CHAR(34)&"_blank"&CHAR(34)&">Click Here To View Website</a>","")&IF(D3="","</td>","")&IF(D3<>"","<a href="&CHAR(34)&D3&CHAR(34)&" target="&CHAR(34)&"_blank"&CHAR(34)&">Click Here To View Website</a>","")&"</td>"
Thanks everyone, I know this is a big ask

edit: I have tried to add the results that formula produced but this site wont let me because I'm guessing it contains html code

Last edited: