Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

Windows 7: Locking certain cells from updating

02 Jun 2012   #1

Windows 7 x64
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 SpecsSystem Spec

02 Jun 2012   #2

Windows 7 Ultimate SP1 64-Bit

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 -
My System SpecsSystem Spec
02 Jun 2012   #3

Windows 7 x64


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...
My System SpecsSystem Spec

25 Jun 2012   #4

Windows 7 x64

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.
My System SpecsSystem Spec

 Locking certain cells from updating

Thread Tools

Similar help and support threads for2: Locking certain cells from updating
Thread Forum
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

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 12:18 AM.
Twitter Facebook Google+

Windows 7 Forums

Seven Forums Android App Seven Forums IOS App

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33