Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered value
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered value
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 "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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered value
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered valu
Thank you, Mike.
"Mike H" wrote: 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 "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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered value
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered valu
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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell to show something different the entered valu
Good to hear.
Gord On Thu, 24 Apr 2008 06:31:01 -0700, TTAmo wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a cell show blank until data is entered | Excel Discussion (Misc queries) | |||
How do I get a cell to show the day of the week when date entered | Excel Discussion (Misc queries) | |||
Border Formatting Disappearing when data entered into cell | Excel Discussion (Misc queries) | |||
Excel will not show all text entered in a Cell merged from 9 rows | Excel Discussion (Misc queries) | |||
numbers being entered show in formula bar but not in cell? | Excel Discussion (Misc queries) |