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

How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out