Protecting Formulas:theoretically the formulas

[Shandsboy]

Hello,

I know this maybe confusing - in essense I have a workbook with a worksheet for each unit 30 worksheets in all. The first worksheet displays formula calculations from the other worksheets via Paste links. The managers from the different units adjust the data on their specific unit(worksheet) and theoretically the formulas should just adjust to the changes (which should only be numerical and job titles) but it seems they keep adding rows and that seems to throw off my formula and i find myself having to reset the formula and paste links on a daily basis. Is there a way to protect these, so even if they do add a row or take one away, Excel does not lose or scramble my formulas?

[Palmetto]

Possibly you can avoid this issue by using Dynamic Named Ranges as the range references in your formula. Dynamic Named ranges automatically expand/contract with addtition and deletion of rows/columns depending how you set it up. You would have to tell us more about the formulas or post a sample workbook to be sure.

If you don't want them add/delete rows then unlock the cells you want them to input data into and turn on worksheet protection. All cells are locked by default, so you only to unlock the input cells. Lock the workbook structure as well. Insert Rows/Columns will not be available when the sheet is protected.

[Shandsboy]

Okay Palmetto, I think this may do the trick, I did a dry run and it worked nicely, although I am not 100% sure what the managers are doing to it. I just notice when the formulas go awry there are extra lines made on some of the units.

I'll see if I can make a Dum-Dum workbook that mimicks it as I can't upload the one we are using, too big and policy violations.

[Shandsboy]

I had posted a previous thread but I did not explain the situation good enough and wanted to try again fresh:

I have a series of workbooks (each workbook represents a particular Unit/Department) that keep track of all the staffs paygrades, licenses, degree and other info all calculated with =SUM(whatever) and =Countif formulas. The results from these formulas are linked to a master workbook that compiles all this info.

Managers go into their individual workbooks and add delete rows depending on the staff changes. which in theory should not mess up the master workbook. When I go into the master workbook it asks if I want to update calculations and if the managers had changed anything in their workbooks it should just change the calculation totals. Instead it seems like it throw all the formulas in that workbook off and the Master workbook displays other info on the spreadsheet. When this happens I have to go through and correct all the formulas and links again, which is almost a daily thing. I tried making the formulas with a range but it is not seeming like it is working too well. any ideas?

I know this maybe confusing and hopefully if I can make a dummy one to post as of right now there is a lot of personal info on them.

[shg]

Threads merged in deference to Palmetto, who has already tried to help. Please continue here.