Excel

[jk_1mclean]

how do you set up a spreadsheet where formulas can be prevented from updating, to be used later. Example a formula is = sum a column and store the value in say a1.

another entry later needs to have the current value in a1 as a new number is entered in the column of sums which creates an updated value.  There must be a way to have a1 remain as current or someway to have that value available from somewhere else.

[#2]

Will the new numbers be entered on a new date or is there the potential that existing numbers will be overwritten.

If added by date then potentially you could do a SUMIFS based on date (relies on person entering a value also entering the date).

If numbers can be overriden then it's only possible with writing VBA (Macro Code)

[#3]

Thank you for the answer.

I am totally new at this and was trying to create a bowling league application.

I created one sheet with the updated stats of all bowlers and league standings.

Another sheet was for entering the scores on a weekly basis, according to a schedule.

As they are updated the league standings are updated.

The problem is that some stats are based on the bowlers previous weeks average, and it updates as soon as current data is entered, and skews the data for some scores.

Is there a way to save that previous week’s average data someplace in order to use it on the current weeks information?

Talk to you soon,

[#4]

I don't exactly understand what you're trying to do sorry, but there's no easy way to stop a formula updating other than copying it and paste it as values.

If you could post an example file to the forum that would help.

[#5]

The following is a layout of the seasonal stats.

The problem is the average column is updated whenever new games are entered into the weekly stats. (Second sheet below)

Is there a  way to preserve the average column value (AVE) somewhere after each weekly input so it can be used to calculate the hcp column and the game1hcp to game3hcp columns on the second sheet the following week.

	Gender	Name	hcp	HGS	HSS	HGH	HSH	gms	Ave	Total Pins
	F	Betty McLean	86	128	352	216.2	615.700	6	114.833	689
	M	Jim Karasimos	93	140	366	236	624.000	9	106.222	956
	M	Jack StevenHaagen	85	146	591	230.75	610.300	9	115.333	1038
	M	John McLean	21	213	591	239.55	670.650	9	186	1674
	F	Verna Mortensen	57	162	450	222.3	629.100	6	146.833	881
	M	Ken Killen	23	218	573	246.8	657.600	6	184.833	1109
	F	Joyce Banks-Stevenhaagen	63	172	425	234.1	611.300	6	140.5	843
	M	Alex McKinnon	102	101	289	######	#DIV/0!	3	96.3333	289

Hits & Misses	96		Game 1	Game 2	Game 3	Team Series Scratch	Team SeriesHcp	Team Game	Team  Game Hcp	Game1Hcp	Game2Hcp	Game3Hcp	# games	Average
Betty McLean	97		118	125	109					205.9	212.9	196.9	3	114.8333
Jim Karasimos	98		140	97	99					274.1	231.1	233.1	3	78
Jack StevenHaagen	99		104	113	135					241.55	250.55	272.55	3	77.22222
John McLean	100		212	184	188					326.15	298.15	302.15	3	120.3333
	101
Vacancy	102
absent	103
Absent	104
Absent	105
Absent	106