copy and paste formulas

[Jim]

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.

 

[ChrisJ]

You need to modify the cell references in your formula - i.e. cell "A3" needs
to be referenced as "$A$3". A formula like that can be copied anywhere
without changing the source cells in the process. Hope that is what you are
looking for.

ChrisJ

 

[tjtjjtjt]

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]

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

 

[tjtjjtjt]

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