Assign values to letters

[offsup]

Dear Mr. Excel,

I'm new to this forum.

I have been trying to assign a number value to each of 3 letter characters but have not found the correct function. Can you help?

Ultimately, one of these 3 letters will be typed into each cell within 1 column and the 3 corresponding numbers will be used in a formula to calculate the product of a second cell multiplied by this number to appear in a 3rd cell.

Any help will be appreciated. I have tried using 'VALUE' but I keep getting an error. Thank you.

offsup

 

[lenze]

Hi and welcome to the board!!

Can you be a little more specific? What are the 3 letter characters and what numbers correspond to each? In stead of a formula or function, would you consider a VBA(code) approach? I think I know what you want, but need to check.

lenze

 

[offsup]

Hi Lenze,

Thank you for your quick response.

In column D, I would like to assign each cell 1 of 3 letters (C, F, P).

When I type 1 of those letters, I would like to create a formula that will substitute each letter for a number (C=5, F=6, P=7).

Later on, I will calculate Column B multiplied by Column D to appear in Column E. Column E=(Column B * Column D)

Let me know. Your help is terrific.

offsup

 

[lenze]

So, you want the entry in column D to automatically change to the corresponding number, or do you want the number displayed elsewhere?

lenze

 

[offsup]

Dear Lenze,

For the sake of future crisises', let me ask you for both instances. The first instance, where Column D should automatically change to the assigned number.

The second instance, where the assigned number Appears in Column D.5 (since I was shortsighted and used Column E in my example.)

Thank you.

offsup

 

[lenze]

This code will will change the letter entered in "D" to the corresponding number

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
Select Case UCase(Target)
    Case "C": Target = 5
    Case "F": Target = 6
    Case "P": Target = 7
    Case Else
        MsgBox "Invalid Entry"
        Application.Undo
End Select
Application.EnableEvents = True
End Sub

This will place the numberic value in column F. Adjust as needed

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
Select Case UCase(Target)
    Case "C": Cells(Target.Row, 6) = 5 'Place Value in Column F (column 6). Change the 6 to use another column
    Case "F": Cells(Target.Row, 6) = 6
    Case "P": Cells(Target.Row, 6) = 7
    Case Else
        MsgBox "Invalid Entry"
        Application.Undo
End Select
Application.EnableEvents = True
End Sub

You can only use 1 of these a a time!!! Place the code in the WorkSheet module. Rightclick the sheet tab and choose "View Code". Paste the code in the white panel.

HTH
lenze