"I need to have long descriptions in my data validation drop down list, but for tidiness, all I really need in the cell afterward is a code associated with the long description. The code alone isn't really helpful to the user, and the description isn't helpful to the people who process orders, they just need to see the code.
Can I make a choice from a drop down and have the result in the cell be something else?"
- The drop down list will have a list of codes in an adjacent column
- After making a choice, the cell will display the adjacent value instead of the originally selected value.
First we create a source range for the DV list on another page.
In the sample file the sheet is called Lists
Then a named range called Descriptions is created for the data validation. This is a dynamic range so it will expand itself as more items are added to the list.
| | =OFFSET(Lists!$A$2,,,COUNTA(Lists!$A:$A)-1,)
The range is now used on the main sheet as a source for a drop down data validation list.
When a choice is made you normally see the item chosen in the cell.
But in this instance, the choice is changed to the associated code from the Lists page.
This is done with a Worksheet_Change
macro active on the sheet. This one is designed to watch column B and make the change.
Nothing says "thanks" like a steak dinner!