Multiple Data Validation

[JStiehl]

I have six columns titled B, M, L, A, S, E. I have a data validation that
prohibits X's from being entered under more than 3 of the 6 columns per row.
I need another data validation that prohibits users from entering their 3 X's
under the columns B, L, and S together (all other combinations are fine).
Any help is much appreciated.

 

[T. Valko]

I have six columns titled B, M, L, A, S, E.

What are the *actual* columns being used? Are the columns a contiguous
range?

Is the only allowable entry a X?

 

[JStiehl]

The actual colums are B4 through G4. Users are supposed to enter an X, but
are not prohibited from entering a different value. Thanks for your help!

 

[T. Valko]

Users are supposed to enter an X, but are not
prohibited from entering a different value.

prohibits X's from being entered under more
than 3 of the 6 columns

So, I guess that means a user can enter 3 Xs and up to 3 other entries for
the other 3 cells?

Try this:

=AND(COUNTIF($B4:$G4,"x")<=3,($B4="x")+($D4="x")+($F4="x")<3)

 

[JStiehl]

Thanks. That worked great for prohibiting entries under B, L, and S
together. I think I was unclear on the other part. Users are entering an x
under columns like a check mark, and they may select a maximum of 3 columns
to enter their x under (from the choices B, M, L, A, S and E). I was using
data validation to prohibit more than 3 column selections using this:
=COUNTA($B$4:$G$4)<4. Is there any way to combine both your formula and this
one with data validation? Thanks so much, I appreciate your help!

 

[T. Valko]

If I were you I'd restrict the user to entering only the single character
"X". Then you could use this:

Select the range B4:G4
Data>Validation
Allow: Custom
Formula:

=AND(OR(B4="x",B4=""),COUNTIF($B4:$G4,"X")<=3,$B4&$D4&$F4<>"xxx")

Uncheck Ignore blank
OK out

Allows only the single character X
Allows only a maximum of 3 Xs in the entire range
Does not allow those 3 Xs to be in B4, D4 and F4

 

[JStiehl]

Thanks! It worked perfectly. I appreciate all of your help!

 

[T. Valko]

You're welcome. Thanks for the feedback!