copy paste changing cell values

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

When you copy a formula (that has a cell reference like A2) from one
cell to another it automatically changes the A2 to say like B2 or A3 or
something. First how do you stop this behavior (not for all values in
the formula, just a few)? And what is the method by which it changes
those values (more just curiosity on this)?
 
Frank, when you see A1 written as $A$1, it is done for that purpose.

It changes those values based on how far you paste the formula from where u
copied it.

Also if there is some data in B1, and you cut and paste it into C1, any
formula that had B1 will automatically change to C1, even with $$.
 
Frank,
Check out Absolute and Relative references in the help files.

Basically putting a $ in front of references changes it to absolute so it won't change when you copy the formula.

If a reference is relative, and it refers to a cell 3 rows down and 2 columns to the right, then wherever you copy it to it will still refer to a cell 3 rows down and 2 columns to the right.

Examples:
A1 - completely relative
$A$1 - completely absolute
$A1 - absolute column, relative row
A$1 - relative column, absolute row

Good Luck,
Mark Graesser
(e-mail address removed)

----- Frank wrote: -----

When you copy a formula (that has a cell reference like A2) from one
cell to another it automatically changes the A2 to say like B2 or A3 or
something. First how do you stop this behavior (not for all values in
the formula, just a few)? And what is the method by which it changes
those values (more just curiosity on this)?
 
Perfect!!! Your explanation it 100 times better then the excel help, it
did not give me anything like this for Absolute

Frank
 
Tip: If you are typing the formula you can use the F4 key to add and delete the $ for the
selected cell address

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Mark Graesser said:
Frank,
Check out Absolute and Relative references in the help files.

Basically putting a $ in front of references changes it to absolute so it won't change when you copy the formula.

If a reference is relative, and it refers to a cell 3 rows down and 2 columns to the right, then wherever you copy it to it
Click to expand...
will still refer to a cell 3 rows down and 2 columns to the right.
 
Thanks Ron, I forget to mention that important tip.

Regards,
Mark Graesser

----- Ron de Bruin wrote: -----

Tip: If you are typing the formula you can use the F4 key to add and delete the $ for the
selected cell address
 
Back
Top