Re: 'Fixing Cell Content'
Conrad: There is no way to 'freeze' a single cell from recalculating without doing the manual 'copy, paste values' process others mentioned.
However, you can conditionally freeze by setting up a 3 column set of formulae which I think will accomplish what you are after.
Here is a simple set of 3 that illustrates the approach I've used in the past when forecasting forward using actuals posted at interim points along the way. The process simply adds 1 to the prior 'plan' unless there is an 'actual' present. Then it adds one to the actual. The first grid below contains the formulae; the second the results. The upper left cell reference is A3 (the one with "=1" as the formula).
Plan Actual Forecast
=1 7 =IF(B3>0,B3,1)
=1+A3 =IF(B4>0,B4,1+C3)
=1+A4 =IF(B5>0,B5,1+C4)
=1+A5 =IF(B6>0,B6,1+C5)
=1+A6 4 =IF(B7>0,B7,1+C6)
=1+A7 =IF(B8>0,B8,1+C7)
=1+A8 =IF(B9>0,B9,1+C8)
=1+A9 3 =IF(B10>0,B10,1+C9)
=1+A10 =IF(B11>0,B11,1+C10)
=1+A11 =IF(B12>0,B12,1+C11)
Plan Actual Forecast
1 7 7
2 8
3 9
4 10
5 4 4
6 5
7 6
8 3 3
9 4
10 5
When everything is said and done, you'll only need to display the 2nd and 3rd column. Or, if you're good at lookup tables, you could enter your actuals somewhere else by date, and have them post on the correct row in the hidden actual column.
Hope this helps.
PS: I hate how the tables don't line up, but I don't have Snippet here at the office.
AI
