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

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.