Create World Peace With an Excel Drop Down List

image Can the sales staff and accounting staff ever work in peace? One group wants to see product descriptions, when entering orders. The other group thinks the descriptions clutter up the worksheet — they just want the product codes. Try this data validation trick, and you might be nominated for next year’s Nobel Peace Prize. (Results not guaranteed.)

Create Drop Down Lists

To make it easier for users to enter data in an Excel workbook, you can create drop down lists in the cells, by using Excel data validation.
In this example the product list is in an Excel Table, and the ProductShow column is a named range — ProdList. The ProdList range is used as the source for the drop down lists on the order entry sheet.

Add the Magic

After the product is selected from the drop down list, the full description is automatically replaced by the product code. How does it happen? It’s the magic of Excel VBA — event code that runs when the worksheet is changed.
The Excel VBA code uses the Match worksheet function to find the row number in the lookup list. It replaces the selected product description with the matching Product Code from that row in the lookup list.
Peace at last! Your co-workers will be happy that they don’t have to memorize the product codes, and the accounting department will be grateful that they get the data in the format they need.

Download the Sample File

To see the Excel VBA code that changes the product name to a product code, go to the Contextures website, and download the sample file: DV004: Data Validation Change. The example used here is the Excel 2007 version, and there is also an Excel 2003 version of the sample file.

Watch the Data Validation Video

Watch this video to see the steps for creating an Excel Table, naming a column in that table, then using that name when creating the data validation drop down list.