Need stupid help again

[Payne]

Hey folks. I am just wondering if I can do something.

Yes, I come up with dumb things, so please bear with me.

Okay, a bit of background for the one helping me.

I run a baseball team/league, and I have been helped by you folks with ratios, and random seedings. Now, I am looking for streaks.

If it is possible, I would like to make a cell show the streak of a certain other cell. I will show example.


A B C D E F G
1 Name Wins Losses Draws Streak
2 Dan 3 0 0 3 wins
3 John 2 1 0 1 loss
3 Paul 1 2 0 2 losses
4 Jim 0 2 1 1 Draw

I hope you get the point. I would like, say, cell E2 to show the streak of Wins/Losses/Draws.

Is there a way of doing this?

In cell E2, show the last input in either cell B2, C2 or D2.

THanks for the help, even if I can't do it.

 

[Payne]

That didn't work the wanted it to, but I can't do that neat cell thing you guys do in here, lol

 

[tactps]

Try this in cell E2:

=IF(D2=0,IF(C2=0,B2&" Wins",C2&" Losses"),D2&" Draws")

Edit: Ignore. Sorry misunderstood the post

 

[DRJ]

Are you doing to track the wins and losses seperately or just list the total like you have above. How would Excel know what the streak is based on the total wins/losses?

 

[Payne]

I guess I am looking for new input into certain cells.

If I input data into Cell B2 3 times, then have D2 Display "3 wins". If, after that, I input data into C2, D2 displays "1 loss".

Like I said, I am not even sure it's possible

 

[DRJ]

Anything is possible. Standby...

 

[Payne]

Standing by.

Well, sitting by, actually.

Thanks for the help.

By the way, tactps, I know the answer of the question in your sig.

I wondered that years ago, and asked.

DRJ-I love the sig. I would like to get a copy, if I could, for sigs in other forums. How do ya get it to be different every time I vome to the page.

Either way, cool sig

 

[tactps]

You could try this (called an event handler):

Press Alt-F11
In the right pane, double click on "Sheet1" (or the name of the sheet you're using).
In the right pane, paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Row < 6 And Target.Column < 5 And Target.Column > 1 Then
If ActiveCell.Offset(1 - ActiveCell.Row, 0).Text = ActiveCell.Offset(0, 6 - ActiveCell.Column).Text Then
ActiveCell.Offset(0, 5 - ActiveCell.Column).Formula = ActiveCell.Offset(0, 5 - ActiveCell.Column).Value + 1
Else
ActiveCell.Offset(0, 5 - ActiveCell.Column).Formula = 1
ActiveCell.Offset(0, 6 - ActiveCell.Column).Formula = ActiveCell.Offset(1 - ActiveCell.Row, 0).Text
End If

Regards
End If
End Sub

 

[DRJ]

Book1
	A	B	C	D	E
1	Name	Win	Loss	Draw	Streak
2	Dan	6	1	1	1Draw
3	John	2	1	0	1Loss
4	Paul	1	2	0	2Loss
5	Jim	3	3	2	1Draw
Sheet1

Right click on the sheet tab and select view code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng             As Range
Dim Streak          As String
Dim Number          As Integer

    Set Rng = Range("B2:D5")
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    Streak = " " & Cells(1, Target.Column).Text
    On Error Resume Next
    Streak = Mid(Cells(Target.Row, 5).Text, Application.WorksheetFunction.Find(" ", Cells(Target.Row, 5).Text, 1), Len(Cells(Target.Row, 5).Text))
    Number = Val(Left(Cells(Target.Row, 5).Text, Application.WorksheetFunction.Find(" ", Cells(Target.Row, 5), 1)))
    On Error GoTo 0
    Application.EnableEvents = False
    Select Case Target.Column
        Case 2 'Wins
            Select Case Streak
                Case " Win"
                    Cells(Target.Row, 5).Value = Number + 1 & " Win"
                Case " Loss"
                    Cells(Target.Row, 5).Value = "1 " & Cells(Target.Column).Text
                Case " Draw"
                    Cells(Target.Row, 5).Value = "1 " & Cells(Target.Column).Text
            End Select
        Case 3 'Losses
            Select Case Streak
                Case " Win"
                    Cells(Target.Row, 5).Value = "1 " & Cells(Target.Column).Text
                Case " Loss"
                    Cells(Target.Row, 5).Value = Number + 1 & " Loss"
                Case " Draw"
                    Cells(Target.Row, 5).Value = "1 " & Cells(Target.Column).Text
            End Select
        Case 4 'Draws
            Select Case Streak
                Case " Win"
                    Cells(Target.Row, 5).Value = "1 " & Cells(Target.Column).Text
                Case " Loss"
                    Cells(Target.Row, 5).Value = "1 " & Cells(Target.Column).Text
                Case " Draw"
                    Cells(Target.Row, 5).Value = Number + 1 & " Draw"
            End Select
    End Select
    Application.EnableEvents = True

End Sub

 

[Payne]

Okay, this is what I got

Compile Error:
Sub or Function not defined.

Then the first line is highlited

I don't know what that means, lol

Sorry, this is for tactps' post. I am trying yours, DRJ