Data Validation‎ > ‎

Replace Current Choice

    
                        Sample File:    DV-ReplaceChoices.xls

PROBLEM:
"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?"

SPECIFICATIONS:
  1. The drop down list will have a list of codes in an adjacent column
  2. After making a choice, the cell will display the adjacent value instead of the originally selected value.
EXAMPLES:


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.

Text Box

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Author:    Jerry Beaucaire,  6/12/2010
'Summary:   Long DV list descriptions are replaced on the fly by shorter codes
Dim cell As Range
Dim cFIND As Range

For Each cell In Target
    Application.EnableEvents = False
    If cell.Column = 2 Then    'columm B changes only
        Set cFIND = Sheets("Lists").Range("A:A").Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        cell.Value = cFIND.Offset(, 1).Value
    End If
    Application.EnableEvents = True
Next cell
    
End Sub



Nothing says "thanks" like a steak dinner!

PayPal - The safer, easier way to pay online!

Comments