Running total help...

[Chiefee]

Hi,

I have googled and also read a few threads on this forum but i am unable to find what i am trying to do.

I have a weekly sheet which displays the total score for predicting football scores. The totals shown are formulated form another sheet from their predictions matched against the results. I cant think of a way to get excel to add these totals together to formulate an overall total score for the season as the weeks pass as when i enter the new predictions/results the totals will change accordingly to the current week. Everything i try just adds the current weeks score.

With this i currently manually click the sort button to work out positions, is there a way for excel to automatically do this for me?

Also once the overall total sheet is formulated, is these a way to notify of position changes each week. eg, Mark - down 5, Paul - up 2, Martin, Same.

I have downloaded the Xl2BB and hopefully attached my weekly sheet so i hope this helps.

Any help is much appreciated.

Regards

Paul

Football Prediction V1.xlsm
	B	C	D	E	F	G	H	I
5			Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Total Points
6	1st		Mark	3	0	0	0	12
7	2nd		Paul	2	0	0	0	8
8	3rd		Martin	1	0	2	0	8
9	4th		Nigel	1	0	1	1	7
10	5th		Craig	1	0	0	1	5
11	6th		Paul	0	1	0	2	5
12	7th		Mike	0	1	0	0	3
13	8th		Mark Q	0	0	0	3	3
14	9th		Andy	0	0	0	2	2
15	10th		Lewis	0	0	0	1	1
Weekly Points

Cell Formulas
Range	Formula
E6	E6	=COUNTIF('Player Scores'!AO6:AO14,"4")
F6	F6	=COUNTIF('Player Scores'!AO6:AO14,"3")
G6	G6	=COUNTIF('Player Scores'!AO6:AO14,"2")
H6	H6	=COUNTIF('Player Scores'!AO6:AO14,"1")
I6:I15	I6	=SUM(E6*4,F6*3,G6*2,H6*1)
E7	E7	=COUNTIF('Player Scores'!I6:I14,"4")
F7	F7	=COUNTIF('Player Scores'!I6:I14,"3")
G7	G7	=COUNTIF('Player Scores'!I6:I14,"2")
H7	H7	=COUNTIF('Player Scores'!I6:I14,"1")
E8	E8	=COUNTIF('Player Scores'!Q6:Q14,"4")
F8	F8	=COUNTIF('Player Scores'!Q6:Q14,"3")
G8	G8	=COUNTIF('Player Scores'!Q6:Q14,"2")
H8	H8	=COUNTIF('Player Scores'!Q6:Q14,"1")
E9	E9	=COUNTIF('Player Scores'!AC6:AC14,"4")
F9	F9	=COUNTIF('Player Scores'!AC6:AC14,"3")
G9	G9	=COUNTIF('Player Scores'!AC6:AC14,"2")
H9	H9	=COUNTIF('Player Scores'!AC6:AC14,"1")
E10	E10	=COUNTIF('Player Scores'!AK6:AK14,"4")
F10	F10	=COUNTIF('Player Scores'!AK6:AK14,"3")
G10	G10	=COUNTIF('Player Scores'!AK6:AK14,"2")
H10	H10	=COUNTIF('Player Scores'!AK6:AK14,"1")
E11	E11	=COUNTIF('Player Scores'!AG6:AG14,"4")
F11	F11	=COUNTIF('Player Scores'!AG6:AG14,"3")
G11	G11	=COUNTIF('Player Scores'!AG6:AG14,"2")
H11	H11	=COUNTIF('Player Scores'!AG6:AG14,"1")
E12	E12	=COUNTIF('Player Scores'!M6:M14,"4")
F12	F12	=COUNTIF('Player Scores'!M6:M14,"3")
G12	G12	=COUNTIF('Player Scores'!M6:M14,"2")
H12	H12	=COUNTIF('Player Scores'!M6:M14,"1")
E13	E13	=COUNTIF('Player Scores'!AS5:AS14,"4")
F13	F13	=COUNTIF('Player Scores'!AS5:AS14,"3")
G13	G13	=COUNTIF('Player Scores'!AS5:AS14,"2")
H13	H13	=COUNTIF('Player Scores'!AS5:AS14,"1")
E14	E14	=COUNTIF('Player Scores'!U6:U14,"4")
F14	F14	=COUNTIF('Player Scores'!U6:U14,"3")
G14	G14	=COUNTIF('Player Scores'!U6:U14,"2")
H14	H14	=COUNTIF('Player Scores'!U6:U14,"1")
E15	E15	=COUNTIF('Player Scores'!Y6:Y14,"4")
F15	F15	=COUNTIF('Player Scores'!Y6:Y14,"3")
G15	G15	=COUNTIF('Player Scores'!Y6:Y14,"2")
H15	H15	=COUNTIF('Player Scores'!Y6:Y14,"1")

 

[jasonb75]

Everything i try just adds the current weeks score.

Are you deleting or overwriting the entries for the previous weeks? If so then you would need to use vba to update the sheet once after you have finished entering the new predictions.

Formulas only work with the information that is in the sheet now, not anything that was there but has now been removed.

There is a workaround for this using circular references, but this will only work for a one time change, not for regular changes.