Excel 2010 - How do I put data into a protected sheet?

[Wicca]

I know this may sound like a silly question however, I have a manager that insists on overtyping formulae.

I have a 600 line by 30 column speadsheet and every 4th column contains a link to a seperate worksheet.

eg =Otorohanga!$C4
=Cambridge!$C20
=Rotorua!$C344

Every time he uses the spreadsheet I have to go through and insert these formulae.

He does need to be able to insert new ROWS and insert data so this seems to negate the use of PROTECT WORKSHEET and protectings COLUMNS.

Any easy way around this or do I need to Protect Worksheet and do the changes for him??

Thanks in advance

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[xlhelp]

You can lock the cells with formulae.

On Home tab, click the drop down arrow for Numbers, or Font or Alignment. Click on Protection tab, Uncheck Locked. Click OK.

Hit F5 on your keyboard, click Special, select Formulas. Click OK. On Home tab, click the drop down arrow for Numbers, or Font or Alignment. Click on Protection tab, Check Locked. Click OK.

Protect Sheet.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
Goodbye, Tension!.... Hello, Pension!

 

[Wicca]

Close but not quite. Yes I can insert a new row and that was good.

I did put data into the cells that equated to the non-formula cells however, when I went to put data in the column that had data in, I was unable too as it said the cell was locked.



With respect
Wicca

Believe in yourself, you are worth the investment.

 

[Wicca]

Account Description Total Margin Account Description Total
1/3000/01 SALES TRACTORS formula 2/3000/01 SALES TRACTORS formula
1/3001/01 TRADE ALLOWANCES TRACTOR formula 2/3001/01 TRADE ALLOWANCES TRACTOR formula

1/4000/01 C O S TRACTORS formula 2/4000/01 C O S TRACTORS formula


If I want to insert another row between the FIRST TWO rows I can do so, but I am unable to enter data into the TOTAL COLUMNs of the newly inseted row

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[SkipVought]

Hi,

1) INSERT is NOT a best and accepted proctive for maintaining TABLES. ADD new data in the first empty row BELOW the table and SORT into the desired sequence. INSERT is fraught with hidden woes!

2? UNLOCK the cells in the Totals column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

There is one other way to protect your formulae.

Hit F5 to get to "Go To" dialog box. Select Formulas. Click OK. With cells selected, go to Data Tab. Click on "Data Validation"; under "Allow:" choose Custom. Under "Formula;" type [red]=""[/red]. Click OK. Not foolproof, but does stop someone in their tracks.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
Goodbye, Tension!.... Hello, Pension!

 

[Wicca]

Thanks guys, I think I have the answer . . . you guys are awesome

With respect
Wicca

Believe in yourself, you are worth the investment.