XL - Cerebral Flatulation... 1

[xlhelp]

It should be simple but, I can't seem to think beyond a certain point. I have "Yes" or "No" in column F and I have a text string in column B. If the cell in column F is a "No" then I want verify that either "NoBill" or "No-Bill" is part of the text in the respective cell in column B. So far no sweat. I can get that using IF and SEARCH functions. It errors out and tells me the text string in B is wrong. Where I can't think beyond that point is that when column F is a "Yes", I also want to verify that those two words do not appear in the string.

Most likely forest and trees syndrome...but I am [red]stumped[/red] Sorry couldn't help the pun

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[GlennUK]

=IF(F2="No",IF(AND(B2<>"NoBill",B2<>"No-Bill"),"Error in B",""),IF(F2="Yes",IF(OR(B2="NoBill",B2="No-Bill"),"Error in B",""),""))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlhelp]

Hi, Glenn.

Oh, I wish it was that simple. NoBill or No-Bill is part of a string and not the string itself. Your formula works like a charm if NoBill is the only string in column B.

I use SEARCH function to find the ocurrence since it is not case sensitive.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[lionelhill]

=IF(F1="No", IF(OR(ISNUMBER(SEARCH("No-bill", B1)), ISNUMBER(SEARCH("Nobill", B1))), "nice", "nasty"), IF(OR(ISNUMBER(SEARCH("No-bill", B1)), ISNUMBER(SEARCH("Nobill", B1))), "nasty", "nice"))

 

[xlhelp]

lionelhill, thanks.

Truly appreciate it.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents