Limit The Data Excel Will Accept

BY STANLEY ZAROWIN

Q. I often have to create a weekly job-assignment list. We have 59 employees eligible for the assignments, and I must be careful to use every name only once. Is there some Excel tool that can help me?

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:

 

SPONSORED REPORT

Cybersecurity threats proliferating for midsize and smaller businesses

This report details how SMBs can properly protect private information from breaches, design and implement a cybersecurity policy, and create safeguards for training and education.

QUIZ

News quiz: Senate health care bill in the spotlight

Reports related to the Republican bill to repeal many provisions of the PPACA, other tax issues, and the giant AICPA ENGAGE Conference offered a diverse reading list for June. See how much you know about recent news with this short quiz.