Excel auto calculation formula question.
 

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
------------------------------------------------------------------------
jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152
View this thread: http://www.excelforum.com/showthread...hreadid=377777

Corrected the typo.


--
jckurk
------------------------------------------------------------------------
jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152
View this thread: http://www.excelforum.com/showthread...hreadid=377777

Think through what "relative" reference means. (Sometimes I think it's
easier to see relative vs. absolute references in R1C1 notation). When
you copy across a row like that, relative references will change the
column and not the row in each reference. To get it to look in the
same column but different rows, you would copy down the column. Here's
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.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=377777

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



In article ,
jckurk wrote:

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.

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
------------------------------------------------------------------------
jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152
View this thread: http://www.excelforum.com/showthread...hreadid=377777

JE McGimpsey Wrote:
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?


--
jckurk
------------------------------------------------------------------------
jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152
View this thread: http://www.excelforum.com/showthread...hreadid=377777

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=377777

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.

In article ,
jckurk wrote:

JE McGimpsey Wrote:
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?