NFL Spreadsheet stat pasting problem

[kennybak]

Trying to auto-update NFL stats (PF) and (PA) on my NFL spreadsheet.

__A___B__C____D______E____
1 115 207 15 =A1+C1 =B1+C2
2 148 202 28 =A2+C2 =B2+C1
|
|
(Repeat for other 13 games)

After the game between teams 1 and 2, A1(PF) and B1(PA) change
depending on the score of the game (C1 is team 1's score and C2
is team 2's score). I want to paste special as values columns D and E
over columns A and B.

How do I 1) avoid circular reference and 2) clear columns D and E
(except for the formulas) immediately after the values are pasted to
columns A to B to prevent the new pasted values from being acted on
again in columns D and E?

Of course, maybe there is an easier way to perform these calculations.

Apprciate any help from the forum.

Ken

 

[RalphA]

After entering the values in the range A1:C2, and the formulas in the range D1:E2, with the cursor in D1, hold the shift key down and highlight the range D1:E2. Do a Copy (I use Ctrl+c), go to A1, click on Edit, Paste Special, Values, OK. Yes, you don't want D1:E2 to show a new, recaclulated value. My only suggestion is to just don't take them into account. But, if they bother you, why not place the formulas in the range G1:H2, so they will be further away, or in a similar range beyond the normally visible part of the sheet, so they wll be out of sight?

I could suggest deleting D1:E2 after pasting in A1:B2, but, I assume you wish to use the formulas over and over, so, this would not be an option.

Please let us know if this helps you out, or if you requiere more help.

 

[Brian from Maui]

Trying to auto-update NFL stats (PF) and (PA) on my NFL spreadsheet.

__A___B__C____D______E____
1 115 207 15 =A1+C1 =B1+C2
2 148 202 28 =A2+C2 =B2+C1
|
|
(Repeat for other 13 games)

After the game between teams 1 and 2, A1(PF) and B1(PA) change
depending on the score of the game (C1 is team 1's score and C2
is team 2's score). I want to paste special as values columns D and E
over columns A and B.

How do I 1) avoid circular reference and 2) clear columns D and E
(except for the formulas) immediately after the values are pasted to
columns A to B to prevent the new pasted values from being acted on
again in columns D and E?

Of course, maybe there is an easier way to perform these calculations.

Apprciate any help from the forum.

Ken

Ken,

Far fetched idea. What you can do is record a macro that copies D and E paste special D and E and A and B, enter the formula again in D and E.

 

[RalphA]

Brian has hit the labor-saving idea right on the head! Very good!

Now, as to the other little problem, that you don't want to see the intermediate work-cells D1:E2 change as you update A1:B2, just don't write the formulas in D1:E2 at all, to begin with! Just begin your macro by entering the formulas in D1:E2, then do the Copy of D1:E2, do the Paste Special, Values in A1, and delete D1:E2!

 

[Brian from Maui]

Ken,

A more "prudent" way would be to have a "Score" sheet and "Summary" sheet. Per your example, team 1 and 2 would not play each other every week, but 1 would play 3 and 4 etc. so you'd need to change that layout quite often. You could enter scores in the "Score" sheet and use a Sum or Sumif on the "Summary" sheet. You'd also have a record of the score by each week.

 

[kennybak]

RalphA and Brian,

Thanks for looking at the past unanswered posts and replying to mine. I had gotten discouraged and thought that my subject was either too vague or that it was just not interesting enough to be answered.

I am updating Week 14 now and will examine the solutions you have provided for updating the PF and PA stats.

Ken

 

[dandyrandy]

2002 Regional.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2			American	W	L	RA	RD		National	W	L	RA	RD
3			West Point (MS) P#212	4	1	34	13		Omaha (NE) P#1	3	2	23	15
4			League City (TX) P#554	2	2	26	-13		Jonesboro (AR) P#21	2	2	37	-2
5			Blue Springs (MO) P#499	1	2	17	-9		Springfield (MO) P#69	1	2	22	-8
6			Gonzales (LA) P#81	1	2	16	3		Edmond (OK) P#111	1	2	19	1
7
8	Game	Date	Team	R		RD			Team	R		RD	Time	Pool
9	1	8/15	Gonzales (LA) P#81	4		-1	vs		League City (TX) P#554	5		1	10:00a	Am
10	2	8/15	Omaha (NE) P#1	4		1	vs		Jonesboro (AR) P#21	3		-1	1:00p	Nat
11	3	8/15	Springfield (MO) P#69	4		2	vs		Edmond (OK) P#111	2		-2	5:00p	Nat
12	4	8/15	West Point (MS) P#212	10		10	vs		Blue Springs (MO) P#499	0		-10	8:00p	Am
Scores-Standings

Take a look at this, I think it shows what you're trying to do.
I think Brian is describing something similar to this.
W=Wins L=Loses RA=Runs Allowed RD=Run Differential
Runs Scored can also be done.
dandyrandy