I am trying to store the answer to a calculation before the data used is edited and lost

My situation is, I have created a spreadsheet used to track how activities are going and what stage they are at. So I have two colours (call them 1 and 2) which are used to show when an activity is planned in the year (spaced monthly) and if/when it is done. I have a column I use to calculate the total planned and the total done in the year. My problem is that when I change colour 1 (planned) to colour 2 (done) then I lose the total that is planned in that year. I have created graphs etc which use the data to show how well we have done comparing what we planned to do and what we actually did. So I would like to try and find a way in which the total on the table goes up and down, so as something is planned it is added and as it is done, it becomes done therefore the total of planned goes down and the total of done goes up - but I don't want this data to affect the original planned data. I want to be able to see how many we have planned at the beginning of the year and then how many we have done.

This is a complicated question, I suppose what I am asking is whether there is a way to freeze a graph for a certain part of the data but allow the other part to go up. Say my graph shows planned as one bar and done as another, can I freeze the planned bar and allow the done bar to be edited, as well as then the planned total to be edited in the table without effecting the graph.

This is a long paragraph so apologies but this is really annoying me and I would like to hope there is a solution. Any help given will be much appreciated!

Hi u563058,

I would like to suggest that you can keep targeted data and actual data separate for graphs as reference. You cannot lock specific graph so that it does not take reference.

It would help me to understand your question in a better way if you can upload your file. Please make sure that it does not carry any confidential data