Lock Cell Value once Calculated

The simplest (read "low-tech) way is to periodically lock the data for previous months in the DetailSheet.

Select the range of cells you want to "lock" and CTRL-C to copy it, then click EDIT > PASTE SPECIAL > VALUES and OK.

The Formulas are removed and the values are now there in place of the formulas..

For more "high tech", you could setup macros to do this, but you get the idea.

You can't really "fix" the values in the cell without a macro or doing as JBeaucaire suggested, but what might be a useful approach would be to create a lookup table that shows the start dates for changes in contributions to different accounts. Another advantage of a lookup table would be that it will provide a record for changes you've made.

So for instance say that when you started this spreadsheet, you were contributing $300/month to Savings, 10% of total to Giving, and so on. This started Jan 1st 2005. On Aug 15 of 2007, you changed these to $425 and 12.5%, respectively. Then on Jan 5th 2009, you changed again... and so on.

Make a lookup table somewhere like this (it may be best to put these in a separate sheet; for the example I will call the sheet DistChanges and the table will occupy the range A1:N10 (something like this):

Please Login or Register  to view this content.

Then in your column that keeps track of contributions to Savings, instead of having a formula that adds a $300 deposit every month, you would have the formula "look up" the date of the paycheck (from the SummarySheet), and from that determine how much should be distributed to that "account":

So, where you're normally referencing Cell C$8 on the Details sheet, you would instead substitute the following for that portion of the formula:

Please Login or Register  to view this content.

You would no longer use row 8 on the Details sheet to determine distributions. The TRUE option in VLOOKUP will cause it, in this case, to find the nearest distribution change date that is <= the paycheck date. Let me know if that doesn't make sense.