Automating Data Validation Lists in Excel

Let's first take a look at the Data Validation feature. On the first worksheet of the workbook where you created your list, click on cell A1 and type the word Fruit. Next, select cell A2 and choose Data and then Data Validation. The dialog box in Figure 3 will appear. On the Settings tab, choose List in the Allow field. When the Source field appears, type an equal sign along with the range name that you assigned before, such as =Fruit.
 
Figure 3: Choose List and then specify your range name as the Source on the Settings Tab.
 
On the Input Message tab, enter a title, such as the word Fruit, to describe the input field, along with a message, such as "Choose an item from the list," as shown in Figure 4. It's not necessary to use quotation marks in the Description field.
 
Figure 4: Although optional, the Input Message tab allows you to document the validation.
 
Finally, on the Error Alert tab, enter a Title, such as "Invalid Input," and an Error Message, such as "You must make a selection from the list," as shown in Figure 5. Keep the style set as Stop to prevent the user from bypassing the list items. Click OK to close the Data Validation dialog box.
 
Figure 5: Be sure to complete the Error Alert tab; otherwise, invalid inputs will trigger a generic and cryptic error prompt.
 
Going forward, when you click in cell A2, a note should appear as shown in Figure 6. This documents the spreadsheet. If the user makes a valid choice, he or she will then be able to move to another cell; otherwise, the prompt shown in Figure 7 will stop the user in his or her tracks.
 
Figure 6: Our choices on the Input tab yield a helpful prompt when the user clicks on cell A2. 
 
Figure 7: The user will see this prompt if he or she tries to enter something that doesn't appear on the list.
 
Now, return to your original list, and add two more items, as shown in Figure 8. When you return to your cell with the Data Validation list, the new items should appear automatically. The combination of the table/list features and range names automates this aspect. Within the Data Validation dialog box, you can provide a specific set of cell coordinates in the Source field, but you would then need to manually change the setting if you later expanded the list. Further, Excel 2007 and earlier won't allow you to reference cell coordinates on other worksheets. Best practices in spreadsheet design call for separating supporting lists from the actual data on separate worksheets. Anyone using Excel 2007 and later is no longer faced with the struggle of where to safely position supporting lists for Data Validation on the same worksheet as the actual input is occurring.
 
Figure 8: Since the ultimate source for a validation list is a table/list, new items appear on the Data Validation list automatically.
 
Read more articles by David Ringstrom. 
 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

You may like these other stories...

Event Date: August 28, 2014, 2 pm ET Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to...
When you’re running an accounting office, it's easy to become inundated with paper, forms, and email attachments, especially when tax season rolls around. To prevent your office from becoming completely overwhelmed...
It's not a reality—yet—but accounting software is poised to eliminate accountants. We are at a tipping point for many similar professions: online education replacing professors, legal software replacing...

Upcoming CPE Webinars

Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.