Excel

jk_1mclean
New Contributor

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

 

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,

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.

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.

 

 GenderNamehcpHGSHSSHGHHSHgmsAveTotal Pins
 FBetty McLean86128352216.2615.7006114.833689
 MJim Karasimos93140366236624.0009106.222956
 MJack StevenHaagen85146591230.75610.3009115.3331038
 MJohn McLean21213591239.55670.65091861674
           
 FVerna Mortensen57162450222.3629.1006146.833881
 MKen Killen23218573246.8657.6006184.8331109
 FJoyce Banks-Stevenhaagen63172425234.1611.3006140.5843
 MAlex McKinnon102101289#######DIV/0!396.3333289
           

 

Hits & Misses96 Game 1Game 2Game 3Team Series ScratchTeam SeriesHcpTeam GameTeam  Game HcpGame1HcpGame2HcpGame3Hcp# gamesAverage
Betty McLean97 118125109    205.9212.9196.93114.8333
Jim Karasimos98 1409799    274.1231.1233.1378
Jack StevenHaagen99 104113135    241.55250.55272.55377.22222
John McLean100 212184188    326.15298.15302.153120.3333
 101             
Vacancy102             
absent103             
Absent104             
Absent105             

Absent

 

106            

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies