Prevent duplicates in a Data Validation List

slam said:

I have a column where data is entered via a data validation drop down list that is pulling from =Drivers

How do I also prevent duplicate entries?

Thanks

Click to expand...

T. Valko said:

It's kind of complicated. See this:

http://contextures.com/xlDataVal03.html

Click to expand...

slam said:

Ugh, that is complicated. The way it works is fine, maybe even better than just providing an error message in fact, but I'm having trouble applying it. I can't be changing the cell references correctly.

My Data Validation for =Drivers is currently being entered in B2 through B17. (Pulled from a sheet called "Drivers & Standings", also B2 through B17.

I'll keep playing around with it, but any further help would be great appreciated.

Thanks

Click to expand...

T. Valko said:

Ok, just let us know if you need more help.

I can put up a sample file (when I get more time) if you'd like. I would use slightly different formulas.

Click to expand...

slam said:

I’m so close, but having some little problem.

So again, I’m entering driver names from a drop down in cells B2 through B17 on the ‘Australia’ worksheet

The names are being pulled from B3 through B18 on the ‘Drivers & Standings’ worksheet

Now in attempting to prevent duplicates in B2 through B17 on the ‘Australia’ worksheet, I have added the following

I2 through I17:
=IF(COUNTIF($B$2:$B$17,'Drivers & Standings'!B3)>=1,"",ROW())

J2 through J17:
=IF(ROW('Drivers & Standings'!$B$3:$B$18)-ROW('Drivers & Standings'!B3)+1>COUNT($I$2:$I$17),"",INDEX('Drivers & Standings'!$B$3:$B$18,SMALL($I$2:$I$17,ROW(INDIRECT("1:"&ROWS('Drivers & Standings'!$B$3:$B$18))))))
Entered as an Array Formula

I’ve defined the List of Drivers as “Drivers” with the formula: =OFFSET(Australia!$J$2,0,0,COUNTA(Australia!$J$2:$J$17)-COUNTBLANK(Australia!$J$2:$J$17),1)

So with those formulas, with a blank B2:B17 on the Australia worksheet, I am missing the first driver in column J, and have a #REF! error at the end. However, it does let me enter the other drivers and prevent them being available on the list to enter again.

I can fix the missing driver and #REF! error by changing the first formula above to:

=IF(COUNTIF($B$2:$B$17,'Drivers & Standings'!B3)>=1,"",ROW())-1

Note the “-1” at the end.

However, if I do this, as soon as I entered the first driver in B2, everything errors.

What am I missing?

Thanks

Click to expand...

T. Valko said:

I'll put up a sample file around 7:00 PM my time (U.S. Eastern Standard Time).

Click to expand...

T. Valko said:

OK, here's a small sample file that demonstrates this.

zzzSlam2.xls 18kb

http://cjoint.com/?AHEa7Z0MAQO

Click to expand...

slam said:

Thanks - after moving things around to match my columns, I seem to be having the same problem with yours as I'm having with my formulas above. It doesn't like that my data starts in row 3 instead of row 2 on the source list. See attached:

http://cjoint.com/11au/AHEoK7iU6zd.htm

Click to expand...