Excel and Office RAQ

Limiting entries to a fixed list and preventing duplicate entries in Excel

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

Home page

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.

 

 

Back to other RAQ topics