copy and paste

[Guest]

is there a simple way to copy an exact formula from one cell to past on
another. for example in Cell E1 i have =SUM(A1:A10) , if I copy and paste in
to Cell F1 i get =SUM(B1:B10)

I understand if i lock the cells using $ sign or name the range it will have
the desired effect howvere I have about 200 or so to copy and paste (then
slightly modify the forluma )

Any help much appreciated.

 

[RagDyer]

The trick to copying and pasting formulas to other locations without having
the cell references change, is to "unformulate" the formulas.
They then become simple "text', which will paste *exactly* to the new
locations.
You then "re-formulate" them so that they return to active formulas.

You do this by simply changing the equal sign at the beginning of the
formula to something else, so that XL doesn't recognoze them as formulas.

Select the formulas you wish to copy, then:
<Edit> <Replace>
In the "Find What" box enter " = " (no quotes),
In the "Replace With" box enter " ^^^^= " (no quotes),
Then <ReplaceAll>

Now, just copy and paste to the new location, and then reverse the process
to re-install the solitary equal sign, thus re-activating the formulas.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

is there a simple way to copy an exact formula from one cell to past on
another. for example in Cell E1 i have =SUM(A1:A10) , if I copy and paste in
to Cell F1 i get =SUM(B1:B10)

I understand if i lock the cells using $ sign or name the range it will have
the desired effect howvere I have about 200 or so to copy and paste (then
slightly modify the forluma )

Any help much appreciated.

 

[Guest]

Very clever , excellent tip - thank you