Excel auto calculation formula question.

[jckurk]

I'm using the drag function to drag a formula from horizontally.

My formula is simple =SUM('Enrollment Data'!H15:L15)

When I drag it horzontally I want it to go like this.
=SUM('Enrollment Data'!H15:L15)
=SUM('Enrollment Data'!H16:L16)
=SUM('Enrollment Data'!H17:L17)
=SUM('Enrollment Data'!H18:L18)

Instead it's going like this
=SUM('Enrollment Data'!H15:L15)
=SUM('Enrollment Data'!I15:M15)
=SUM('Enrollment Data'!J15:N15)
=SUM('Enrollment Data'!K15:O15)

I tried using $ infront of the letters but it kept the vale all the way
through. Can anyone help?

Thx.

 

[jckurk]

Corrected the typo.

 

[MrShorty]

Think through what "relative" reference means. (Sometimes I think it'
easier to see relative vs. absolute references in R1C1 notation). Whe
you copy across a row like that, relative references will change th
column and not the row in each reference. To get it to look in th
same column but different rows, you would copy down the column. Here'
what I would do:

1) Make the column reference absolute ($H15)
2) Copy down three rows (assuming you have four blank rows below)
3) Select the three copied rows
4) Select the cell to the right of the original cell
5) Edit -> Paste Special -> check Transpose
6) It should give you the result you're looking for

 

[JE McGimpsey]

One way:

Say this is entered in column A:

=SUM(OFFSET('Enrollment Data'!$H$15,COLUMN()-COLUMN($A:$A),0,1,5))


If it's entered in a different column, change $A:$A to that column
reference

 

[jckurk]

I can't drag the formula vertically because I have other things below
that I have formulas in.

I also can't make H15 absolute because I need it to change horizontally
for each cell (H16, H17, H18 etc.)

 

[jckurk]

One way:

Say this is entered in column A:

=SUM(OFFSET('Enrollment Data'!$H$15,COLUMN()-COLUMN($A:$A),0,1,5))


If it's entered in a different column, change $A:$A to that column
reference

Uhmm this works. I have no idea why. I don't get it. How is that
working?

 

[swatsp0p]

Regardless of where your formula is placed, enter this:

=SUM(OFFSET('Enrollment
Data'!$H$16:$J$16,(COLUMN(n1)-COLUMN($n$1)),0))

Change the (n1) to match the actual column you enter the formula in,
e.g. A1, B1, etc.

Good Luck

Bruce

 

[JE McGimpsey]

When placed in column A, the formula resolves to

=SUM(OFFSET('Enrollment Data'!$H$15,1-1,0,1,5))

which sums a 1 row, 5 column range starting at H15.

When you copy it to column B, the formula resolves to

=SUM(OFFSET('Enrollment Data'!$H$15,2-1,0,1,5))

which sums a 1 row, 5 column range starting one row down from H15. And
so on.