Highlighted
New Contributor

# Excel

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.

4 Replies
Highlighted

# Re: Excel

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)
Highlighted

# Re: Excel

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,

Highlighted

# Re: Excel

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.
Highlighted

# Re: Excel

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