A. The solution is to use Excel’s Validation tool. Once you set it up, which takes only a few minutes, you can reuse it every week. Begin by placing all the names in a worksheet; for this example I’ll use 10 names. Give the list a defined name such as ValidNames ; to do that, highlight the list and place your cursor in the Name Box , which is in the upper left corner of the Excel screen shown below:
Now highlight the cells where you will enter the selected names, providing just enough space to eventually hold the entire list. Since there are 10 names, let’s select cells A1:A10.
Click on Data and Validation to bring up the Data Validation dialog box. Click on the Settings tab, and in the Allow drop-down list, select Custom . Then, in the Formula box, enter this:
=AND(COUNTIF(ValidNames,A1)=1,COUNTIF($A$1:$A$10,A1)=1)
Now click on OK .
To test the formula, try entering Tony’s name twice. You should get the message below:
