Limiting entries to a fixed list and preventing duplicate entries in Excel
Q: I want to limit with data validation the entry a user makes in a range of cells so that only a name appearing in a lookup list can be entered. But I also want to prevent any of the names in that list being entered more than once. Can this be done?
A: Yes, it can.
Assuming A1:A10 contains the list of allowable names and the names are keyed in by the user in Column D, then select D1:D10 and from the Data menu, choose Validation to display this dialogue below. Make sure you change the 'Allow' pulldown to 'Custom':
In the 'Formula' field, enter the following:
=AND(COUNTIF($A$1:$A$10,D1)=1,COUNTIF($D$1:$D$10,D1)=1)
Done.