Hi guys!

After beating my head into the wall for the past hour, I though I'd
come and ask the experts about the best way to solve my problem. I
have a simple worksheet that looks like so:

15 15
15 30
15 45
15 60
15 75

The first column contains '15' and the second column does the formula
=b1+a2, =b2+a3, and so on. The only row that isn't like this is the
first row in which the formula is just =a1.

My question is, how do I make it so that if I delete, say, the 3rd row,
that my formulas will adjust automatically? I've attempted to play
around with absolute references, but I still had problems when I
deleted rows. Granted this is an extremely simple example, but
hopefully, I'll be able to extend the solution to my very complex
spreadsheet.

Thanks,

Rich

Try this in B1:

=SUM($A$1:A1)

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

wrote in message
oups.com...
Hi guys!

After beating my head into the wall for the past hour, I though I'd
come and ask the experts about the best way to solve my problem. I
have a simple worksheet that looks like so:

15 15
15 30
15 45
15 60
15 75

The first column contains '15' and the second column does the formula
=b1+a2, =b2+a3, and so on. The only row that isn't like this is the
first row in which the formula is just =a1.

My question is, how do I make it so that if I delete, say, the 3rd row,
that my formulas will adjust automatically? I've attempted to play
around with absolute references, but I still had problems when I
deleted rows. Granted this is an extremely simple example, but
hopefully, I'll be able to extend the solution to my very complex
spreadsheet.

Thanks,

Rich

So would the formula in b2 be: =SUM($B$1:B1)??

The formula you gave me takes the value in A1 and adds to....A1?

No , exactly as RD said.

The formula adds all cells starting at A1 until A of the current row.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
ups.com...
So would the formula in b2 be: =SUM($B$1:B1)??

The formula you gave me takes the value in A1 and adds to....A1?

Ok, I think my example may have been a bit too simplistic. Let's try
one a little more complex.

Row 1: 18 18
Row 2: 14 32
Row 3: 10 42
Row 4: 3 45
Row 5: 20 65
Row 6: 5 70

So formula in B1 is =SUM($A$1:A1). Formula in B2 is.....
=SUM($B$1:B1)?

No, B1 is =SUM($A$1:A1) and just copy down.

The $A$1 anchors it to the first cell, but as you copy down the second A1 is
incremented, so it sums to the current row.

Try it and see.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
oups.com...
Ok, I think my example may have been a bit too simplistic. Let's try
one a little more complex.

Row 1: 18 18
Row 2: 14 32
Row 3: 10 42
Row 4: 3 45
Row 5: 20 65
Row 6: 5 70

So formula in B1 is =SUM($A$1:A1). Formula in B2 is.....
=SUM($B$1:B1)?

Please just *TRY* as suggested.
THEN, make comments!

With numbers in Column A, from A1 to A100,

Enter formula in B1:

=SUM($A$1:A1)

And drag down to copy.

NOW ... post back with a comments.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

wrote in message
ups.com...
So would the formula in b2 be: =SUM($B$1:B1)??

The formula you gave me takes the value in A1 and adds to....A1?

I did try. According to your directions, I got '15' all the way down.

The formula, as you copy it down, does this:

=sum($A$1:A1) which takes the value in A1
=sum($A$1:A2) which sums the values in A1 and A2
=sum($A$1:A3) which sums the values in A1 to A3

etc.

If you delete a row the formulas will be adjusted.

This is what you had anyway.

Hans

There, that clears things up! Thank you!

-Rich

No. He gave you the formula =SUM($A1:A1) in cell B1. When you copy
it to B2 it becomes SUM($A$1:A2). Any cell in column B will add all the
items in column A, regardless of what Rows you delete or add. ed