Validate Whole Number Series with Exclusions
 

I need to validate an entry to ensure that it is a whole number which falls
within a certain series but with some entries excluded. For example, it must
be a whole number which falls between 1000 and 1099, but where 1050 is
excluded.

If I use validation -- whole number -- I cannot take into account the
exclusion.

If I use validation -- custom -- =AND(A1=1000,A1<=1099,A1<1050) then I
get the exclusion check, but decimal entries can slip by.

I'm using Excel 2003.

Thanks

Validate Whole Number Series with Exclusions
 

To only allow whole numbers, modify your formula to:

=AND(A1=1000,A1<=1099,A1<1050,A1=INT(A1))

HTH,
Bernie
MS Excel MVP


"QLT1120" wrote in message
...
I need to validate an entry to ensure that it is a whole number which falls
within a certain series but with some entries excluded. For example, it must
be a whole number which falls between 1000 and 1099, but where 1050 is
excluded.

If I use validation -- whole number -- I cannot take into account the
exclusion.

If I use validation -- custom -- =AND(A1=1000,A1<=1099,A1<1050) then I
get the exclusion check, but decimal entries can slip by.

I'm using Excel 2003.

Thanks

Validate Whole Number Series with Exclusions
 

It works! Thank you!

"Bernie Deitrick" wrote:

To only allow whole numbers, modify your formula to:

=AND(A1=1000,A1<=1099,A1<1050,A1=INT(A1))

HTH,
Bernie
MS Excel MVP


"QLT1120" wrote in message
...
I need to validate an entry to ensure that it is a whole number which falls
within a certain series but with some entries excluded. For example, it must
be a whole number which falls between 1000 and 1099, but where 1050 is
excluded.

If I use validation -- whole number -- I cannot take into account the
exclusion.

If I use validation -- custom -- =AND(A1=1000,A1<=1099,A1<1050) then I
get the exclusion check, but decimal entries can slip by.

I'm using Excel 2003.

Thanks