Selecetive replication of forumlas
 

I have a forumla I need to replicate to adjoining cells but it's a little
more complicated that dragging the corner down or accross to the next cells.
This is the forumula:

=SUMIF(September!B4:B500,C61,September!D4:D500)

When I drag it down to the next cell, it copies it thus:

=SUMIF(September!B5:B501,C62,September!D5:D501)
=SUMIF(September!B6:B502,C63,September!D6:D502)
and so on

What I want to do is have it copy like this:

=SUMIF(September!B4:B500,C62,September!D4:D500)
=SUMIF(September!B4:B500,C63,September!D4:D500)
and so on

I have A LOT of cells that this forumla need to be copied to and if I have
to manually change them all, it's going to take weeks. Any ideas?

In your original formula, apply "anchors" to the cell references, comme
ca:
=3DSUMIF(September!$B$4:$B$500,C61,S=ADeptember!$D $4:$D$500)

This prevents Excel from shifting the columnar cell references as you
copy the formula to other cells.

The magical $ sign does the trick. Put it in front a row number to keep it
the same during a fill up or down, or the column number for a lateral fill,
or both.

=SUMIF(September!$B$5:$B$501,C62,September!$D$5:$D $501)

Roy





"brodiemac" wrote:

I have a forumla I need to replicate to adjoining cells but it's a little
more complicated that dragging the corner down or accross to the next cells.
This is the forumula:

=SUMIF(September!B4:B500,C61,September!D4:D500)

When I drag it down to the next cell, it copies it thus:

=SUMIF(September!B5:B501,C62,September!D5:D501)
=SUMIF(September!B6:B502,C63,September!D6:D502)
and so on

What I want to do is have it copy like this:

=SUMIF(September!B4:B500,C62,September!D4:D500)
=SUMIF(September!B4:B500,C63,September!D4:D500)
and so on

I have A LOT of cells that this forumla need to be copied to and if I have
to manually change them all, it's going to take weeks. Any ideas?

I modified the wrong part of your post, use the correct row refernces of 4
and 500, not mine of 5 and 501... ;)



"Roy Wagner" wrote:

The magical $ sign does the trick. Put it in front a row number to keep it
the same during a fill up or down, or the column number for a lateral fill,
or both.

=SUMIF(September!$B$5:$B$501,C62,September!$D$5:$D $501)

Roy





"brodiemac" wrote:

I have a forumla I need to replicate to adjoining cells but it's a little
more complicated that dragging the corner down or accross to the next cells.
This is the forumula:

=SUMIF(September!B4:B500,C61,September!D4:D500)

When I drag it down to the next cell, it copies it thus:

=SUMIF(September!B5:B501,C62,September!D5:D501)
=SUMIF(September!B6:B502,C63,September!D6:D502)
and so on

What I want to do is have it copy like this:

=SUMIF(September!B4:B500,C62,September!D4:D500)
=SUMIF(September!B4:B500,C63,September!D4:D500)
and so on

I have A LOT of cells that this forumla need to be copied to and if I have
to manually change them all, it's going to take weeks. Any ideas?

Man, everyone here who has given me answers is makeing me look really good at
work. Thanks all, your the best and this forum ROCKS!!!!

"Dave O" wrote:

In your original formula, apply "anchors" to the cell references, comme
ca:
=SUMIF(September!$B$4:$B$500,C61,SÂ*eptember!$D$4: $D$500)

This prevents Excel from shifting the columnar cell references as you
copy the formula to other cells.