Gloria, I believe we need a bit more info.
So, let's say you have a column of Letter Grades
("A","B","C"...) and column of Weight values.
How is it you want to apply these weights? I
would expect that an "A" would indicate a numeric
score from 90 to 100. To which of these scores
would you want to "map" an "A"? As you can see
it's much more self-evident going from a score
to a letter grade. Going the other way requires
some additional logic.
Numeric Values to Letter Grades (& vice versa)
Hi Gloria
The best approach for your problem would be a macro placed with the Sheet_Change event, like the one below;
To place it in right click on the sheet name tab of the sheet you will be using. Then paste in the code below:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com
'assigns a numeric value to a grade.
'Dependent on column.
Dim WatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("A1:H65536")
If Not Intersect(Target, WatchRange) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ResetMe
Select Case Target 'Determin grade
'Assign the grade value, depending on Column
Case "A", "a"
Select Case Target.Column 'C to D
Case 1
Target = 25
Case 2
Target = 35
Case 3
Target = 45
Case 4
Target = 55
Case Else 'E to H
Target = 65
End Select
Target.NumberFormat = """A"""
'Assign the grade value, depending on Column
Case "B", "b"
Select Case Target.Column 'C to D
Case 1
Target = 15
Case 2
Target = 25
Case 3
Target = 35
Case 4
Target = 45
Case Else 'E to H
Target = 55
End Select
Target.NumberFormat = """B"""
'Assign the grade value, depending on Column
Case "C", "c"
Select Case Target.Column 'C to D
Case 1
Target = 10
Case 2
Target = 20
Case 3
Target = 30
Case 4
Target = 40
Case Else 'E to H
Target = 50
End Select
Target.NumberFormat = """C"""
'Assign the grade value, depending on Column
Case "D", "d"
Select Case Target.Column 'C to D
Case 1
Target = 5
Case 2
Target = 10
Case 3
Target = 15
Case 4
Target = 20
Case Else 'E to H
Target = 25
End Select
Target.NumberFormat = """D"""
End Select 'Target grade
End If 'WatchRange intersects Target
ResetMe:
Application.EnableEvents = True
Set WatchRange = Nothing
End Sub
Now, make the needed changes to the range to monitor for A,B,C or D. Then put in the values for each grade depending on column.
Push Alt+Q to return to Excel and save.
What will happen is each time you type in any one of the letters the code will change it to it's correct numeric value (depending on Column). It will then apply a custom format so that the letter you type still appears.
So in short you may type "A" or "a" in column A and it will change to 25, but still appear as "A". You can see it's real (or underlying) value by clicking in the cell and looking in the Formula bar.
Any problems or question, let me know.
Dave
OzGrid Business Applications