Drop Down List HELP

[coach_veto]

Hi,
My problem sounds very simple but I cannot figure out how to fix it with regular formulas or VBA code.

Example: Let's say I have a drop down list that applies to cells A1:A50. My choices within the list are "United States," "United Kingdom," and "Ireland." Here's my issue: When I pick 1 of my 3 options from the list, I want "US," "UK," or "IR" to appear. So when I click on "United States" from my list in cell A1, I would want "US" to populate instead. If I picked "Ireland" instead though, I would want "IR" to appear and so on...

How can this be done with either formulas or VBA code?

Thanks guys

 

[Dryver14]

could you put them in a table and reference the cell with a vlookup?

 

[coach_veto]

Thought of using vlookup, but I cannot enter a formula in the cells that have the drop down list. If I could, I think it might also create a circular reference because the text I want ("US") depends on what is picked from the list in the active cell (United States/United Kingdom/Ireland).

 

[PATSYS]

Right click on your sheetname, choose View Code, paste the below code:

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Range("A1")) Is Nothing Then

Select Case Range("A1").Value

Case "United States"

   Range("A1").NumberFormat = ";;;""US"""

Case "United Kingdom"

   Range("A1").NumberFormat = ";;;""UK"""

Case "Ireland"

   Range("A1").NumberFormat = ";;;""IR"""


End Select

End If

End Sub

 

[Dryver14]

What I am saying is put the VLookup in the cell you want to populate

 

[Caribeiro77]

Another option?

Private Sub Worksheet_Change(ByVal Target As Range)


country = Target.Value


Select Case country
   Case "United States"
      Target.Value = "US"
   Case "United Kingdom"
      Target.Value = "UK"
   Case "Ireland"
      Target.Value = "IR"
 End Select


End Sub

 

[coach_veto]

wow thank you! It worked! Really appreciate it

 

[coach_veto]

This code only applies to cell A1. It won't work for the other cells in column A. If I wanted to fix that, how would I change your code? For example, what if I wanted this to apply to cells A1:A50?

 

[coach_veto]

@Caribeiro77 that code works for all the cells that have a list. Thank you! it answered my previous question regarding PATSYS answer

 

[Caribeiro77]

Which one? Mine or PATSYS, cause i tried mine and it work everywhere...