Here's a method for doing this that I took out of a post from a frequent
poster, "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.


tj


"Jim" wrote:


How would I paste a series of cells?
Example: a1:a9

"tjtjjtjt" wrote:

Another way:
Double-Click on the cell and Copy the data from the Formula Bar.
Press Escape.
Double-Click on the destination cell and Paste.
Press Enter.

tj


"Jim" wrote:

HI,

I currently use this formula:

=SUMPRODUCT(--(AM3:AM10000=TIME(0,0,0)),--(AM3:AM10000<=TIME(0,29,59)),--(AO3:AO10000=1))

How do I paste this into another cell without changing the formula? The
paste special function formulas is changing the destination.