|02 Jun 2012||#1|
| || |
Locking certain cells from updating
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?
|My System Specs|
|Similar help and support threads for2: Locking certain cells from updating|
|SmartDefrag2: Half the screen filled with black cells||Performance & Maintenance|
|Excel: How do I make some cells remain the same when pulling it down?||Microsoft Office|
|In Excel 2007 why doesn't locking and protecting cells work?||Microsoft Office|
|Cells contaning Fri 16 Jul 10 and similar as text||Microsoft Office|
|Quickly fill blank cells in Excel.||Microsoft Office|
|Delete empty cells in worksheet||Microsoft Office|
|Excel un password cells||Microsoft Office|