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'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.

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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?

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?

[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



In article <[email protected]>,
jckurk <[email protected]> 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.

[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.

In article <[email protected]>,
jckurk <[email protected]> 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?