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

Test yourself on these often confused words

The spelling checker on your word processing program can do only so much to flag problems. Your best insurance is to learn the troublesome words that trip up writers and use them correctly by the standards of formal, written English.