Help with data validation numbers not being able to be used more than oncee

**643coach** · 12-07-2009, 10:56 PM

I'm trying to create a spreadsheet where I want to apply point values (1-50) to cells, but I don't want a number to be used more than once. I've put the numbers in a data validation list. I'd like the cell to change color if a number is used more than once as well.

I can't figure this out...can someone please help??

**Paul** · 12-07-2009, 11:20 PM

Hi coach, and welcome to the forum.

Let's say your range of cells (50 of them) are A1:B25. Select those cells and open the Data Validation dialog. Choose custom, and enter the following formula:

=AND(ISNUMBER(A1),A1=INT(A1),A1>=1,A1<=50,COUNTIF($A$1:$B$25,A1)<=1)

This formula will check to see if the value you entered is a number, that it is an integer between 1 and 50, and that the number entered has only been entered once.

If you can't enter a number more than once, there would be no way to color a cell in which a duplicate number is entered. In the Data Validation window, on the Error Alert tab, you can type in a custom Error message for the user, too.

**teylyn** · 12-08-2009, 01:50 AM

Another option is with a shrinking data validation list that presents the user only with those values that are still available. Takes a lot of trial and error out of data entry.

see attached

hth