Instead of selecting a product code in an Excel drop down list, it's usually easier to select a product name instead. However, your pricing calculations might use the product code, instead of the product name, so you need that information.
With a bit of programming in Excel, you can show a description in a combo box, but enter the matching code for the selected item into the cell. You've seen other versions of my data validation combo box code:
- Double-click on data validation cell to open combo box
- Click on data validation cell to open combo box
- Use named lists with data validation combo box
Keep reading, to see how the new combo box codes example works.
Select From a Combo Box
I've uploaded a new sample file on the Contextures website, which lets you select a month name, or weekday name, from an Excel combo box.
When you leave the combo box, the month number, or weekday code is automatically entered in the cell, instead of the full description.
The Named Ranges
In the data validation cells, lists are allowed, and they refer to the named ranges – DayList and MonthList – where the short codes are stored.
Another range is created for each list, to include the description column, and these names end with "Codes" – DayListCodes and MonthListCodes.
These "Codes" ranges are used to fill the combo box, which is formatted with 2 columns. The first column has a width of zero, so the codes aren't visible.
Download the Sample File
To test the combo box codes example, and see how it works, you can download the sample file from the Contextures website. In the Data Validation section look for DV0057 – Data Validation Combobox Codes. The file is in Excel 2007/2010 format, and contains macros.