HOW CAN I PROTECT A FORMULA IN EXCEL
 

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 ?

HOW CAN I PROTECT A FORMULA IN EXCEL
 

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

HOW CAN I PROTECT A FORMULA IN EXCEL
 

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!

" wrote:

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

HOW CAN I PROTECT A FORMULA IN EXCEL
 

Try this:

=INDIRECT("D1")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kingsley" wrote in message
...
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!

" wrote:

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

HOW CAN I PROTECT A FORMULA IN EXCEL
 

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

"Kingsley" wrote:

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 ?

HOW CAN I PROTECT A FORMULA IN EXCEL
 

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" wrote:

Try this:

=INDIRECT("D1")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kingsley" wrote in message
...
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!

" wrote:

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

HOW CAN I PROTECT A FORMULA IN EXCEL
 

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kingsley" wrote in message
...
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" wrote:

Try this:

=INDIRECT("D1")

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Kingsley" wrote in message
...
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!

" wrote:

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