Data validation

[Lincoln]

I would like to restrict user to input 3 digits (i.e 123, 513) and each input is unique, there are total of 100 observations

I have tried to put in formula =COUNTIF($B$2:$B$101,B2) =1, but I dont know how to make the "multiple validation"

Thank you in advance

[ConneXionLost]

In the Data Validation dialog box, choose the "Whole Number" option, then select a range "between" 100 and 999.

Cheers,

[Beamer]

ConneXionLost's idea would indeed solve half your problem, but, if I'm reading your question correctly, you want to make sure that the same number can't be chosen twice.

If that is correct, then I suggest a helper column showing all numbers within your desired range that automatically removes any number that has been used. Then another column will re-list all those numbers using small to remove the blanks.
Then make the data validation use that list. (That will probably only work if it is a drop-down list).

Also, all previously used numbers would have to remain on the same sheet (to be searched for a match), if that is not the case then I think VBA will be needed.

[NoSparks]

cross poster

[pecoflyer]

Thread closed