A Drop-down List To The Rescue

BY STANLEY ZAROWIN

Key to Instructions

To help readers follow the instructions in this article, we used two different typefaces:

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer.


Each week I distribute Excel worksheets to a group of line managers who aren’t handy with Excel. I ask them to enter certain product numbers in a single worksheet cell. Because they often accidentally put the information in the wrong cell or enter incorrect numbers, I end up with a weekly headache. Short of prescribing aspirin, what do you suggest I do?

A drop-down list would solve your problem. You can set up each worksheet with a list of all the available product numbers; when a manager’s cursor passes over the target cell, the list with all numbers drops down. When they click on one of the numbers, Excel stores it and the list disappears.

Setting up the list is a snap. Begin by placing each product number in numerical order in contiguous cells. Then highlight the cells and give the collection a name ( Products ) by entering the name in the Name Box above cell A1 (see screenshot below) or by clicking on Insert , Name , Define .

Now go to the worksheet you’ll be distributing to the managers and select the first cell that will contain the drop-down list. Go to Data and click on Validation , opening the Data Validation dialog box, with the Settings tab displayed.

Press the arrow to the right of Allow and display List . Be sure the Ignore blank and In-cell dropdown boxes are checked. In the Source box type the name you gave to the list preceded by an equal sign: =Products . The equal sign is critical. Then click on OK .

When you pass your cursor over the box that contains a downward pointing arrow, this is what will appear:

Option : Instead of typing the names into the worksheet, you can enter them directly into the Data Validation dialog box. Type them in the Source box, with each name separated by a comma. Do not use an equal sign.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

COLUMN

Deflecting clients’ requests for defense and indemnity

Client requests for defense and indemnity by the CPA firm are on the rise. Requests for such clauses are unnecessary and unfair, and, in some cases, are unenforceable.