How to increment a number in one cell based on a letter in another

[Seniornetman]

I want to increment a number in one cell based on the letter P, T or A in another cell. How can I go about this?

 

[Scott Huish]

Do you mean like this?

Book1
	A	B
1	Letter
2	P	P1
3	T	T1
4	P	P2
5	A	A1
6	T	T2
7	P	P3
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2="","",A2&COUNTIF($A$2:A2,A2))

 

[Seniornetman]

Do you mean like this?

Book1
	A	B
1	Letter
2	P	P1
3	T	T1
4	P	P2
5	A	A1
6	T	T2
7	P	P3
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2="","",A2&COUNTIF($A$2:A2,A2))

Do you mean like this?

Book1
	A	B
1	Letter
2	P	P1
3	T	T1
4	P	P2
5	A	A1
6	T	T2
7	P	P3
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2="","",A2&COUNTIF($A$2:A2,A2))

No I mean the number 1 in A and it increments to 2 when P is entered in B

 

[Scott Huish]

That will take VBA. With this if you enter P, T or A in B2. A2 will increment by 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, s
Set c = Intersect(Target, Range("B2"))
If c Is Nothing Then Exit Sub
s = UCase(c)
If s = "P" Or s = "T" Or s = "A" Then
    Application.EnableEvents = False
        Range("A2") = Range("A2") + 1
    Application.EnableEvents = True
End If
End Sub

 

[Seniornetman]

No I mean the number 1 in A and it increments to 2 when P is entered in B

That will take VBA. With this if you enter P, T or A in B2. A2 will increment by 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, s
Set c = Intersect(Target, Range("B2"))
If c Is Nothing Then Exit Sub
s = UCase(c)
If s = "P" Or s = "T" Or s = "A" Then
    Application.EnableEvents = False
        Range("A2") = Range("A2") + 1
    Application.EnableEvents = True
End If
End Sub

OK I see what you have done here and I assume since my cells are G2 and L2 all I have to do change those B2 to L2 and A2 to g2 and it should work, correct? Also I assume since this will need to work in 38 cells I will re edit and put it in all 38?

 

[Scott Huish]

No, the code will just need to be modified to work with that entire range, give me a minute.

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, s
Set c = Intersect(Target, Range("L2:L38"))
If c Is Nothing Then Exit Sub
For Each d In c
    s = UCase(d)
    If s = "P" Or s = "T" Or s = "A" Then
        Application.EnableEvents = False
            d.Offset(, -6) = d.Offset(, -6) + 1
        Application.EnableEvents = True
    End If
Next
End Sub