Validation Formula - Multi Condition

[feltonam]

Hi

I want to write a validation formula to show an error message for the following conditions: if the value in a certain cell <> "S" then the values all of the following 5 cells in the row must be Null, else if the value does equal 'S' then all of the values in the 5 cells must not be Null.

I've made a start as below and got the message to show if the cell equals S and all 5 are Null, but as soon as I put a value in any of the 5 it disappears. Any help appreciated.

=IF(AND(H11="S",I11="",J11="",K11="",L11=""),"Columns I - M must be completed if Absence type is S","")

 

[SkipVought]

How about this...
[tt]
=IF(G11<>"S",COUNTA(H11:M11)=0,COUNTA(H11:M11)=6)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[buitenhek]

=if(or(h11="S",I11="S",J11="S",K11="S",L11="S"),"","Enter Info")

Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures

 

[SkipVought]

feltonam,

I assumed that you were using Data > Validation - Custom. Yes?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[feltonam]

Thanks for reply. Can't use Custom Validation, as i'm using dropdown lists for values in columns I-L. Column L is the last one with data, and I'm trying to enter this formula in column M so it shows an error message if data has been incorrectly entered.

Perhaps I haven't explained properly:

If an S is entered in H11, All of cells I11-L11 must contain data.

If an S is not entered in column H, none of cells I11 - L11 must contain data.

 

[SkipVought]

What does that all mean?

"I want to write a validation formula to show an error message for the following conditions"

WHERE does this message appear?

Exactly WHEN does this message appear? Think thru the process of how the data is cleared/entered.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}