Drop Down Lists

[snmark]

Dear sirs,
Drop down list of names , each name reflecting a number. So that when selecting a name it shows its number. For example, in calculating compound interest, the drop down list of names: Annually, Bi annually, Quarterly, Monthly, Weekly , Daily are reflected in the cell by the numbers 1, 2, 4, 12, 52, and 365 respectably.

Hope Ive explained it clearly enough.

Thanks with regards

Stephen

 

[James006]

Hi,

As long as you have your List as a reference ... nothing will prevent you from using a simple VLookup ... :wink:

 

[Peter_SSs]

If I have understood correctly that you want the number to appear in the actual cell where the data validation is then that is possible as follows.

- Set up the Data Validation cell(s) with your 6 text choices. I have used cell E1.
- Use this Worksheet_Change code

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$E$1" Then
    Application.EnableEvents = False
    If Len(Target.Value) > 0 Then Target.Value = Split("1 2 4 12 52 365")(Application.Match(Target.Value, Array("Annually", "Bi annually", "Quarterly", "Monthly", "Weekly", "Daily"), 0) - 1)
    Application.EnableEvents = True
  End If
End Sub

 

[Peter_SSs]

Another option, if you want the user to be able to see both the text and the number, but only end up with the number is to set up your DV with a list like I have in column L, use that list in your Data validation and use this Worksheet_Change code instead.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$M$1" Then
    Application.EnableEvents = False
    Target.Value = Mid(Target.Value, InStr(1, Target.Value, ":") + 1)
    Application.EnableEvents = True
  End If
End Sub

Excel Workbook
	L	M
1	Annually:1	4
2	Bi annually:2
3	Quarterly:4
4	Monthly:12
5	Weekly:52
6	Daily:365
DV Replace (2)

#VALUE!

 

[Yongle]

another VBA option for you

Data validation in cell
Allow : List
Source : 1 Annually,2 Bi Annually,4 Quarterly,12 Monthly,52 Weekly,365 Daily

so that the user sees these option in cell
1 Annually
2 Bi Annually
4 Quarterly
12 Monthly
52 Weekly
365 Daily

VBA
Place code in sheet module
RightClick on sheet tab \ select ViewCode \ paste code into VBA editor \ back to Excel with {ALT}{F11}
Save workbook as macro-enabled

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Target = Val(Target)
        Application.EnableEvents = True
    End If
End Sub

 

[snmark]

Thank you Peter and Yongle. I will try both and let you know if it’s successful.

Very grateful indeed.

Kind regards

Stephen

 

[snmark]

Thank you Peter and Yongle. I will try both and let you know if it’s successful.

Very grateful indeed.

Kind regards

Stephen

I've tried both, no success with Peters, but success with Yongle's. Not say Peter, that anything was wrong with your suggestion, as its very likely that my application of it was suspect.

Thank you both for your prompt and professional response, very grateful indeed.

cheers, with regards

Stephen