Locking cell data from change

[sezingsheim]

I have set up payroll for the small company I work for in Excel 2007, with
separate worksheets for each employees info (address, withholding allowances,
wage rates, vac time accrued, etc), tax tables, weekly paystubs, monthly and
yearly reports. The problem I am having is that I want to lock all data
previously entered once I close a month/quarter so that there is no way to
change the data in any way since I will have already filed the taxes for that
quarter. I know that I can lock specific cells and worksheets from changes
being input, but is there a way to keep "cascade" changes from taking effect
in those cells and worksheets? For instance, an employee gets a raise, I put
the new rate in the employee card (which is the cell that the weekly paystubs
pull from to calculate the gross pay), and oops, now the previous pay periods
reflect the new wages and not the old wages that they were actually paid. Is
there a way to do this?

 

[JLatham]

You cannot prevent formulas from updating as long as they remain formulas,
even if you lock the cells and protect the worksheet.

BUT, what you can do is convert the formulas to their values. Here is how:

You'll need to unprotect the sheet. Then select the cells that you do not
want to 'cascade update' in the future. Use
Edit --> Copy
and without selecting anything else, use
Edit --> Paste Special and check the "Values" option.
Protect your sheet again.

The formulas in the selected cells will have been replaced with the values
that were previously displayed.

I realize that this will become tedious depending on the number of employees
you have. Recording a macro from the point where you unprotect the sheet
through the process until you protect it again will help. Assuming the range
of cells to be manipulated on each sheet is the same, then you could just
select each sheet in turn and run that macro.

Even that can be tedious, so once you have things worked out a little, you
may want to return here and ask for some help in fully automating the
process. With enough information, the entire process could be automated for
all appropriate sheets in your workbook, and even be 'robust' enough to
handle each month's different group of cells.