Protect formulas but still need to be able to copy and insert rows

[juliaharry]

Hi,

I have a template in Excel 2010 which is used by main people to fill in budget for a pitch for a marketing job etc....that has several sections in it for departments and employees hourly cost for a job and then the rows are split into stages. I want to be able to protect hourly rates in the rows and some of the formulas in the columns. Unfortunately when I do this protection just on these cells then it is not possible to copy and insert new rows and also hide or close down the grouping for printing purposes.

How do I get round this... I still want to protect the cells and I am not sure if a macro would work as individual users may need to insert at different stages ie rows of the spreadsheet and hide different areas.

many thanks

 

[dspel79082]

Hi Julia, one trick I've used that may work for your scenario, is to put the actual formulas on a hidden sheet, and reference the formulas. You can protect the cell with simple data validation.
Example:

the formula in sheet1 A1 is =A2*A3
on a hidden sheet2 cell A1 put the formula =sheet1!A2*sheet1!A3

on sheet1 set data validation on cell A1 to list, with no in cell dropdown =sheet2!a1

your formula will not be visible and can't be altered, but users will have the freedom to sort, filter, etc.