Locking certain cells from updating

All,

I created a five year pro forma financial model for my business. The spreadsheet makes extensive use of worksheet formulas and macros. I also added a sheet to track actuals (numbers extracted, by hand, from QuickBooks monthly).

Certain formulas in the spreadsheet are based on values that do not change over time. For example, I assume that my widgets will sell for some unit price and monthly revenue forecasts are calculated as the projected sales volume times the unit price.

Here's the problem - let's assume that my sales price changes at some point in time, after sales revenue has been generated. If I simply change the price, which is applied globally, then the historical 'projections' will be wrong.

Just to be clear: When the model was first built, it assumed that widgets sell for $100 apiece. Assume that unit sales are the same as the month number. Thus, my revenue for the first six months would be $100, $200, $300, $400, $500, and $600. Assume that for the first three months, I hit my revenue numbers. In this case, the sheet that tracks actuals would show a $0 difference, for each of the first three months, between projected revenue and actual revenue. Since business is doing so well, I decide to increase the price of the widgets to $150 on a forward-going basis. Suddenly, the spreadsheet now shows that I failed to meet projections for the first three months, when, in fact, I did meet them!

It seems to me that there should be some way to lock cells in the spreadsheet based on the current date. For example, when I open the spreadsheet in June, I should be able to programmatically protect cells from May and earlier. Is this possible? an you please provide a pointer to how this can be accomplished?

Thanks.

I'm not sure if this is what you're after, but this article describes how to lock cells by using a protected worksheet.

Lock cells in a worksheet - Excel - Office.com

SeaVixen:

That is the general idea, but I need to do that via a macro. Guess I could record a macro to see what that does...

There is no way to do this. What I have done is written a macro to replace cells with their values - painful, but it gets the job done.