If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 ? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 ? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|