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
GRoston

Windows 7 x64
 
 
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.


My System SpecsSystem Spec
.

02 Jun 2012   #2
seavixen32

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

Windows 7 x64
 
 

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


25 Jun 2012   #4
GRoston

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
Reply

 Locking certain cells from updating




Thread Tools





Similar help and support threads
Thread Forum
Character length in a cell or cells
Hi guys, just wanted to ask if any of you can help me out in excel. Basically what I wanna do is, if I copied a text or a sentence and I would want to paste it on a cell, i'd like the cell to remove any characters if it is over than 40 excluding spaces. Any kind of help/advice would be...
Microsoft Office
Format cells in excel 2007
I have a spreadsheet made in excel 2007. In one of the cells is a number with three zeros in front of it and one zero at the end. However it always drops the zeros and shows only the other numbers. I select the cell and click on "format cells", but there is nothing I see that will show all the...
Microsoft Office
In Excel 2007 why doesn't locking and protecting cells work?
This is literally driving me nuts. I've created a workbook with about 15 worksheets and prior to sending to the customer I want to lock the cells so that they can't be changed without a password. So, I first select the cells I don't want to be altered and lock them. Then I go to "protect...
Microsoft Office
Cells contaning Fri 16 Jul 10 and similar as text
I have a s/sheet ( Excel2007), whose source data was taken from a link. The date column is in the format as per title. In that form it will not sort itself or associated columns in to date order. Has anyone a way to make the data in the column sort itself as I want? Also with the example...
Microsoft Office
Delete empty cells in worksheet
I am a newbie to Excel and have created a small calculation worksheet. It only occupies a small section of the worksheet and I would like to know how to only show that part and not showing the empty cells. I am using Microsoft Office 2007. Thanks jack
Microsoft Office
Excel un password cells
Hi I have a excel sheet that some cells are password protected i have no idea what the password is any ideas how i can find the password or any software that will find it, so i can change the cell contents. Thanks
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 19:37.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App