If and formlua looking for any text

**eddienole** · 05-17-2012, 11:06 AM

I'm working on an error flag to visually identify when a note wasn't entered. I'm trying to dumb this down to the lowest level and the formula below works for all errors I want to catch, but it returns false if they enter the note correctly in R9. The notes can vary so I don't have enough Ifs avilabel to catch all the possibilities. Is there and IF I can add that looks for any text in the cell?
Something like IF(R9=anytext),""). I don't want the false to confuse anyone. Unfortunately it has!

Current formula
=IF(AND(I9=""),"",IF(AND(Summary!$P$13="",I9="Y",R9=""),"ERROR",IF(AND(Summary!$P$13="Note",I9="Y",R9=""),"ERROR",IF(AND(Summary!$P$13="Task",I9="Y",R9=""),"ERROR"))))

**NBVC** · 05-17-2012, 11:11 AM

Try it like this:

=IF(I9="","",IF(OR(AND(Summary!$P$13="",I9="Y",R9=""),AND(Summary!$P$13="Note",I9="Y",R9=""), AND(Summary!$P$13="Task",I9="Y",R9="")),"ERROR",R9))

or shorter still...

=IF(I9="","",IF(AND(R9="",I9="Y",OR(Summary!$P$13={"","Note","Task"})),"ERROR",R9))

or replace the last R9 with "" if you want a blank if they enter note correctly....