Enter this formula in a helper column.

=LOOKUP(B1,{1,2,3,4},{"L","M","H","over 3"})

If you don't want to use a helper colulmn you could use event code to change the
value in-cell when entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
On Error GoTo endit:
Application.EnableEvents = False
End If
nums = Array(1, 2, 3, 4) 'adjust to suit
vals = Array("L", "M", "H", "Over 3") 'adjust to suit

For Each rr In r
ival = 0
For i = LBound(nums) To UBound(nums)
If rr.Value = nums(i) Then
ival = vals(i)
End If
Next
If ival 0 Then
rr.Value = ival
End If
Next
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 23 Apr 2008 09:23:01 -0700, TTAmo
wrote:

Can someone please help?

I'd like to see an "H" in a cell when I enter a 3; "M" for 2; "L" for 1.
I've tried custome formatting, but haven't figured it out.

Any suggestions?

Thanks.