how to paste formula and retain all or part of the cell references

[Seppo Laukkanen]

I have following kind of problem:

I want copy a cell from A5 to B5.
In A5 I have a formula that takes values from A2 and E5, i.e. "=A2/E5".
In the pasted cell the formula changes to corresponding values: "=B2/F5".

In some cases this might be what I want, but in current situation I would
like to change only column to corresponding - I would liket the resulting
cell to include formula: "=B2/E5".
How can I do this?
Of course, in case of such simple formula it wouldn't be a problem but if I
have very long formula with lots of references, it's a nightmare..
Also, is it possible to retain whole formula: the copied cell B5 would look
like: "=A2/E5"?

Thanks!
Seppo

 

[JMay]

Check out Absolute and Relative Cell Addressing in Help.
A cell is usually say C1 <<< Totally relative
But it can be $C1 << Column Absolute - Row Relative
C$1 << Column Relative - Row Absolute
$C$1 << Both Column and Row Absolute
When you copy the Cell C1 depending on how you have it entered (any of the 4
option above) the $ sign freezes the reference and doesn't change in your
destination cell(s).

 

[Dave Peterson]

And if you really don't want the absolute reference style ($a$1 instead of a1),
you can select the formula in the formula bar and copy it from there. Then
paste into the cell.

This works pretty nice for a few formulas. If you have a giant range that you
to keep as-is, you can select your range,
Edit|replace
=
with
$$$$=

Now all your formulas are text. copy and paste your range. Then change the
$$$= back to = and excel will see them as formulas and recalculate.