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.

 

[tactps]

Oops:

Delete the word "Regards" (lol).

 

[Payne]

DRJ, It isn't doing....anything

 

[tactps]

Jacob,

Could you shrink the cases by using "Case Win" and "Case Else" under each of the 3 scenarios?

 

[Payne]

TC-The new one is messing my sheet up totally

 

[DRJ]

Jacob,

Could you shrink the cases by using "Case Win" and "Case Else" under each of the 3 scenarios?

Yes, I wrote it out completely for illustration purposes.

 

[DRJ]

TC-The new one is messing my sheet up totally

Set up an example sheet like I have pasted here. Then put the code in the sheet code section. Then change the win loss draw values and the streak will update.

 

[Payne]

The TC one is doing good, just putting it in a line down.

And, I don't understand what you asked me to do, DRJ.

Now I am frusterated at myself for feeling so stupid

 

[Payne]

ECW.xls
	A	B	C	D	E	F
1	Name	Wins	Losses	Draws
2	Dave	1	4	1	2	Wins
3	AdamPearce	3	9	0	1	Draws
4	Stan	0	3	0	1	Losses
5	AceSpalding	9	5	0	2	Losses
6	Reggie	6	9	0
7	Michael	7	8	0
Win Loss

Okay?

 

[tactps]

It shouldn't (works fine for me).

Paste this in over the top and try again:

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
End If
End Sub

 

[Payne]

Same thing. I input a number in B2, it shows up in E3. It is perfect, except I want it in E2.

Also, it works for the first 6 rows, what do I change to get, say at least 75 rows?