Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking cell data from change
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking cell data from change
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. "sezingsheim" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking a cell after data entry (Excel 2003) | Excel Discussion (Misc queries) | |||
Locking Data into a cell | Excel Discussion (Misc queries) | |||
Locking Cell Once Data is entered | Excel Worksheet Functions | |||
Locking in Formula cell to follow its cells that contain calc data | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) |