Drop-down List - Populate with a different value from the one selected

[TanKS]

Hi all,

Let's say I have two lists:

Status	Abbreviation
Present	P
Outstation	OS
On Course	Cse
Attached Out	AO

<colgroup><col><col></colgroup><tbody>
</tbody>

What the user should see when he clicks on the drop-down list are: Present, Outstation, On Course and Attached Out.
Let's say he selects Present, and instead of populating "Present" in the field, "P" is used.

Thank you in advance!

 

[AliGW]

Not possible. The cell with the drop-down will populate with what is on the drop-down list, but you can get an adjacent cell to show the abbreviation.

I don't know if there is a VBA workaround.

 

[jeffreybrown]

Try this...

Lookup table on Sheet2 in A2:B5

      -----A------ -----B------
  1   Status       Abbreviation
  2   Present      P           
  3   Outstation   OS          
  4   On Course    Cse         
  5   Attached Out AO

On Sheet1, copy this code to the Sheet module

In column A you have your data validation list starting in cell A2

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range: Set rng = Target.Parent.Range("A:A")
    Dim rng2 As Range: Set rng2 = Sheets("Sheet2").Range("A2:B5")
    Dim MyResult As Variant
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Cells(Target.Row, 1) = WorksheetFunction.VLookup(Target.Value, rng2, 2, 0)
    Application.EnableEvents = True
End Sub

Paste code in a Sheet module

• Where to paste code
• Highlight macro to copy >> Ctrl + C >> Open your workbook
• Right click on sheet tab >> View Code >> opens the Visual Basic Editor (VBE)
• Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Now simply make a change in target cell

 

[TanKS]

Try this...

Lookup table on Sheet2 in A2:B5

      -----A------ -----B------
  1   Status       Abbreviation
  2   Present      P           
  3   Outstation   OS          
  4   On Course    Cse         
  5   Attached Out AO

On Sheet1, copy this code to the Sheet module

In column A you have your data validation list starting in cell A2

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range: Set rng = Target.Parent.Range("A:A")
    Dim rng2 As Range: Set rng2 = Sheets("Sheet2").Range("A2:B5")
    Dim MyResult As Variant
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Cells(Target.Row, 1) = WorksheetFunction.VLookup(Target.Value, rng2, 2, 0)
    Application.EnableEvents = True
End Sub

Paste code in a Sheet module

• Where to paste code
• Highlight macro to copy >> Ctrl + C >> Open your workbook
• Right click on sheet tab >> View Code >> opens the Visual Basic Editor (VBE)
• Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Now simply make a change in target cell

Thanks Jeff, your codes work perfectly well!!!

 

[jeffreybrown]

You're welcome. Glad it helped.