Protecting formulas

**val2009** · 09-23-2008, 11:44 PM

Hi all,

I have a 2 part question relating to formula protection.

1. How do you protect formulas in cells and allow flexibility in the sheet to insert rows. So once you insert the new row, you're able to copy in the formula.

2. Is there a way of copying a formula back into a cell automatically once it has been overrided by a manual entry.

any assistance on this would be most appreciated,

Cheers,

Val

**Paul** · 09-23-2008, 11:52 PM

Hi Val,

1. When protecting the worksheet, there are a series of options that you can let users do while it's protected including the ability to Insert Rows. Be sure to check that box when applying protection.

2. You can use a macro (VBA) to check when a certain cell (or cells) change, then reset them to whatever you want when they do change.

**val2009** · 09-24-2008, 12:04 AM

thanks paul!

with 2, if you reset back to its orginal formula will you lose the last number that has been added?

I'd prefer to keep the number.

cheers,

Val

**Paul** · 09-24-2008, 12:24 AM

Where would you hope to "keep" the number? If you'd like to insert the formula so that its result is that new number, you're going to need quite a bit more code than simply 2 or 3 lines. (Of course, that also depends on the complexity of the formula you want in the cell, and of course if the formula is referencing other cells, those cell values would need to be changed to give the result you want to "keep".)

**val2009** · 09-24-2008, 12:54 AM

thanks paul,

my forumla is very simple.

in terms of keeping the number - what I mean is to keep the number as part of the referenced formula.

Example, there is a formula in B1 (e.g. C1*A1).
If you manually place an integer into B1 - the formula is lost.

Is there a way of keeping the manual integer in B1 and not lose the formula.

cheers,