HOW CAN I PROTECT A FORMULA IN EXCEL

[Guest]

I have 2 questions. I have a formula in a cell and want to copy it to an
adjacent cell without the formula changing based on the cell location. Ex:
In A1 I have the formula =M1+T1, when I copy this to B1 it changes the
formula to N1+U1, if I copy it to A2 it changes it to M2+T2. How can I copy
the cell without changing the formula ?
My second question is I have a cell with the formula =D1. When I drag or cut
another cell into D1 it changes the formula to =#REF!. Is there a way to
maintain the original formula of =D1 ?

 

[carimfam]

Hi Kingsley,

Use the function key F4 when typing your formula in order to get
absolute references,
such as =$M$1+$T$1 or =$D$1 ...

HTH
Cheers
Carim

 

[Guest]

Thank you. This worked for the first problem, but not for the second. When I
drag another cell, or cut a cell and paste it, into the referenced cell it
still changes the formula to #REF!

 

[Ragdyer]

Try this:

=INDIRECT("D1")

 

[Guest]

In front of the constant cell value place the dollar sign in your formula.

example:
$M1+$T1

to totally fix the formula:
$M$1+$T$1

 

[Guest]

Thank you, this worked great!! I have an additional question, When I drag a
new cell into the referenced cell a box pops up and asks me if I want to
replace the contents of the destination cell and I have to click yes or
cancel. Is there a way to turn this off so the update is automatic ?

 

[Ragdyer]

Try this:

<Tools> <Options> <Edit> tab,
And *UNCHECK* the obvious:
"Alert Before Overwriting Cells"