Copying SUM formula across that sums a column range dwn

[Boatman]

I need to copy a SUM formula across a worksheet. The formula is summing a range in a column in another worksheet. When I copy the SUM formula across, the column reference in the formula needs to remain the same, but the rows in the range need to change.

[Yogi Anand]

On 2002-04-21 10:42, Boatman wrote:
I need to copy a SUM formula across a worksheet. The formula is summing a range in a column in another worksheet. When I copy the SUM formula across, the column reference in the formula needs to remain the same, but the rows in the range need to change.

Use a mixed Cell reference in your formula ... preceding the Column lable with $ will make the Columnb reference absolute.
For example in $A$1 ... both Column and Row references are absolute
and in $A1 ... Column reference is absolute and Row reference is relative,
so you could have 4 possible combinations ...
A1; $A1; A$1; and $A$1 ... relative, mixed, and absolute.

HTH

[eliW]

On 2002-04-21 10:42, Boatman wrote:
I need to copy a SUM formula across a worksheet. The formula is summing a range in a column in another worksheet. When I copy the SUM formula across, the column reference in the formula needs to remain the same, but the rows in the range need to change.

=SUM(Sheet2!$D1:$D10)
when you put this formula in A1 it will sum the first 10 cells in column D in sheet2. Now if you want to copy the formula to B6 or C6 etc. it will sum cells D6:D15 in sheet2
Eli

[Boatman]

Attn: EliW

The formula you suggest is the one I'm using.
I'm trying to copy the formula from A1 to cells in the same row. I make the column absolute and the rows relative, but the formula when copied across the same row does not change. I could see the row references changing by one row if I dropped down one row, but how do you make them change without dropping down?

[Chris Davison]

Boatman,

you need to tell us what you want them to change by...... if the change is uniform we can probably cope but if it's "random" that might be trickier.....

give us specific references

[Chris Davison]

via email

=SUM(Sheet2!$D1:$D10) This appears in cell A1 in Sheet 1

I want to copy the above formula to cells B1, C1, D1 etc. in
Sheet 1. The
results I want is as follows:

=SUM(Sheet2!$D2:$D11) in cell B1 Sheet 1
=SUM(Sheet2!$D3:$D12) in cell C1 Sheet 1
=SUM(Sheet2!$D4:$D13) in cell D1 Sheet 1

In other words I need the row range to change 1 row down, not
the column,
when I copy the formula across columns in sheet 1.

Thanks.
-----------------------------

=SUM(INDIRECT("Sheet2!$D"&COLUMN(A:A)):INDIRECT("Sheet2!$D"&COLUMN(J:J)))

and copy accross

[Yogi Anand]

=SUM(INDIRECT("Sheet2!$D"&COLUMN(A:A)):INDIRECT("Sheet2!$D"&COLUMN(J:J)))


for another take on Chris' very versatile formula, you could salso use

=SUM(INDIRECT("Sheet2!$D"&COLUMN(A:A)):INDIRECT("Sheet2!$D10"))

Admittedly, Chris' is more comprehensive!

[Chris Davison]

Yogi mate,

your D10 is static isn't it ? we need it to increment by 1 each time it's copied to the next cell to the right, hence my "column" function....

(we need the rows of the range to drop, so the last reference's row needs to increase the same as the first's)

Chris

[Yogi Anand]

Hi Chris:
You are absolutely right, my friend!

Regards!
Yogi Anand