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.)
See the workbook details below, and there’s a video with step-by-step instructions at the end of the page.
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.
Product Lookup Table
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 Excel VBA 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.
Drop Down List VBA Code
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
You can 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.
___________
A very useful piece of code, allowing dual description then switching to product ID.
This is exactly what I am looking for but it is not working. When a selection is made to the drop down list on the DataEntry sheet it populates the cell with the full ProductShow cell data and not just the ID#. For example, if Desk is selected it returns this: Desk — ID# 40732. Does anyone have a possible solution??
@John, make sure that you enable macros when you open the file.
Debra, Thank you for the reply! However when the file was initially opened the allow Macro warning showed and was enabled. Now when opened it does not show and in the Developer tab, View Macros it does not show any listed. Security is set to enable all Macros as well. Is there something else that needs to be done to activate the Macro? THX
@John, if you’re using Excel 2010, a document can automatically be set as Trusted, after you’ve opened it once, and enabled macros.
The macro in this file is on the worksheet module, so you won’t see it listed in the View Macros window.
If you right-click on the sheet tab, and click View Code, do you see code there?
Debra, thanks for your prompt reply and assistance and all is good now! Using this example with your help has allowed me to create custom product codes based on data entered into a modified Codes table and DataEntry sheet which is exactly what I needed! This site and your help are awesome!