Keep Same Cell Reference

[Susan]

If you are referencing a cell in a formula and you want it to remain the same
when you copy and paste the formula into another cell, how do you enter it?

Susan

 

[Mike H]

Susan,

use the dollar sign =$a$1

Mike

 

[Gord Dibben]

$A$1

See help for more on relative and absolute references.


Gord Dibben MS Excel MVP

 

[Larry]

This was never a problem in Excel 2003. I am trying to copy and paste a row
in new rows I have inserted. I have tried relative references, but then the
formula in the cell I copied remain the same. The self help section for this
says that if a formula is copied and pasted the relationship will not change,
but it does.
Here is my formula: =SUM((B138-B137)/G138)
I used this to automatically figure my gas mileage and ran out of rows, so I
need to insert rows with the same formulas across the row. When I copy the
row, the formula changes to this for the copied cell =SUM((B138-B136)/G138).
None of this ever happened with Excel 2003. I could create new rows, copy and
paste the formulas and the reference would remain the same.

 

[Tyro]

You were using Excel 2003. What version are you using now? If it's Excel
2007, it works the same as Excel 2003 in regards to addresses.

Tyro

 

[Cimjet]

Hi Larry
Try this: =SUM(($B$138-$B$136)-$G$138)
HTH
Cimjet

 

[Larry]

I have to use the paste formula function and that appears to work. Can't do
just paste. I am serious. I did Ctrl-C and Ctrl-V in 2003 and everything was
good. Using 2007 now.