Data Validation - List - Data "NOT BETWEEN" - Source Possible?

D

Duke2U

Setting up electronic log sheet where serial numbers are entered. Trying to
use VALIDATION to check previous entry's for duplicates. If "Data" was able
to be change to "Not Between" that would be great, but this window is greyed
out in Data Validation. Is there another way?
 
M

Max

Duke2U said:
Setting up electronic log sheet where serial numbers are entered. Trying to
use VALIDATION to check previous entry's for duplicates. If "Data" was able
to be change to "Not Between" that would be great, but this window is greyed
out in Data Validation. Is there another way?
Click to expand...

Try this ..

Select the data input range, say A2:A20 (with A2 active)
Then click Data > Validation > Custom
Formula:
=AND(COUNTIF($A$2:A2,A2)<2,A2>=1000,A2<=2000)
Click OK

The formula will disallow duplicate entries down the range, and also ensures
that only numbers between 1000 to 2000 [inclusive] are permitted (as an
example)

Test it out and adapt to suit ..
 
Top