Multiple Data Validation
 

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.

Multiple Data Validation
 

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.

Multiple Data Validation
 

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" wrote:

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.

Multiple Data Validation
 

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)

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.

Multiple Data Validation
 

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.

Multiple Data Validation
 

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
DataValidation
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

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.

Multiple Data Validation
 

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

"T. Valko" wrote:

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
DataValidation
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

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.

Multiple Data Validation
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
Thanks! It worked perfectly. I appreciate all of your help!

"T. Valko" wrote:

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
DataValidation
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

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"JStiehl" wrote in message
...
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.