Formatting a cell to show something different the entered value

[TTAmo]

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.

 

[Mike H]

I'm not sure about formatting but you cn do it like this.
Right click the sheet tab, view code and paste this in on the right.
it currently works A1 - A10 but you can chage this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case UCase(Target.Value)
Case "H"
Target.Value = 3
Case "M"
Target.Value = 2
Case "L"
Target.Value = 1
End Select
End If
End Sub

Mike

 

[Ian]

The simplest option would be to use Autocorrect to substitue 3 with H etc.
The drawback with this is that it's universal (ie in ANY workbook 3 would be
replaced by H, though 23 wouldn't be replaced by 2H, if that's any help.

The alternative would be to use code to simulate Autocorrect. This would be
specific to the workbook.

Ian

 

[TTAmo]

Thank you, Mike.

I'm not sure about formatting but you cn do it like this.
Right click the sheet tab, view code and paste this in on the right.
it currently works A1 - A10 but you can chage this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case UCase(Target.Value)
Case "H"
Target.Value = 3
Case "M"
Target.Value = 2
Case "L"
Target.Value = 1
End Select
End If
End Sub

Mike

 

[Gord Dibben]

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

 

[TTAmo]

Worked like a charm.

I was able to incorporate my existing formula (=(ROUND(AVERAGE(F3:G3),0)))
into the formula below. I substituted my formula for the LOOKUP value (cell
B1 in the formula below) and was able to eliminate the need for a helper
column without getting into the code.

The final formula is:
=LOOKUP((ROUND(AVERAGE(F3:G3),0)),{1,2,3,4},{"L","M","H","over 3"})

Thanks for your help!!!

 

[Gord Dibben]

Good to hear.

Gord

Worked like a charm.

I was able to incorporate my existing formula (=(ROUND(AVERAGE(F3:G3),0)))
into the formula below. I substituted my formula for the LOOKUP value (cell
B1 in the formula below) and was able to eliminate the need for a helper
column without getting into the code.

The final formula is:
=LOOKUP((ROUND(AVERAGE(F3:G3),0)),{1,2,3,4},{"L","M","H","over 3"})

Thanks for your help!!!