protect formulas only
 

Hi -
I have a massive spreadsheet with formulas in it and variable input data. I
don't want to protect the whole worksheet because there is variable data that
effects formula output.
I wondered.... is there a way to protect my formulas only so that I don't
fat finger and ruin a formula (that has taken time and consideration) and
that I can only change the variable data input?

For example, if I have a formula such that if Q1 actual sales are greater
than or equal to 110% of Q1 Plan then the customer gets a reward of 5% of Q1
actual sales. So I don't want to ever change the formula, but I might want
to change Q1 actual data if it changes due to adujustments or incorrect input
on my part.

I tried copy, paste special, formulas, but that did not freeze the formula
cell.

Please let me know.
Thank you very much.
--
Thanks!

protect formulas only
 

Cells can be locked or unlocked. But the "lockedness" of a cell doesn't do much
until you protect the sheet.

So if I were doing this manually, I'd do:

Unprotect the worksheet (tools|protection|unprotect sheet)
Select all the cells
Format|cells|protection tab|check unlocked.
Then with all the cells still selected
Edit|goto|special|check Formulas
format|cells|protection tab|checked locked.

Then reprotect the worksheet.

But I'd also lock the cells that contain headers/instructions/notes--stuff that
shouldn't be changed.

It may be easier to lock all the cells and just unlock the cells you want to be
able to change.

And just a reminder--when you protect a worksheet, there are lots of things that
are disabled. You may want to test to see that bothers you.

learningaccess wrote:

Hi -
I have a massive spreadsheet with formulas in it and variable input data. I
don't want to protect the whole worksheet because there is variable data that
effects formula output.
I wondered.... is there a way to protect my formulas only so that I don't
fat finger and ruin a formula (that has taken time and consideration) and
that I can only change the variable data input?

For example, if I have a formula such that if Q1 actual sales are greater
than or equal to 110% of Q1 Plan then the customer gets a reward of 5% of Q1
actual sales. So I don't want to ever change the formula, but I might want
to change Q1 actual data if it changes due to adujustments or incorrect input
on my part.

I tried copy, paste special, formulas, but that did not freeze the formula
cell.

Please let me know.
Thank you very much.
--
Thanks!

--

Dave Peterson