Need open ended cell for Sum range.

[Guest]

I have numerical data in column A rows 1:14.

I'm adding it up in row 15. Sum=(A1:A14)

Now I've been asked to insert more rows with data above row A14.

What can I put in my Sum formula in place of A14 so when I insert
a new row, any numerical data above it in column A automatically adds up?
Sum=(A1:????)

I don't want to have to go and change it to sum=(A1:A17) if I
insert 3 rows.... I have a huge file to create with sections like this. User
may have to insert new rows. They need to all add up.

 

[Bob Phillips]

If you goto Tools>Options>Edit and check the Extend list formats and
formulas box, it should work automatically..

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Richard Buttrey]

I have numerical data in column A rows 1:14.

I'm adding it up in row 15. Sum=(A1:A14)

Now I've been asked to insert more rows with data above row A14.

What can I put in my Sum formula in place of A14 so when I insert
a new row, any numerical data above it in column A automatically adds up?
Sum=(A1:????)

I don't want to have to go and change it to sum=(A1:A17) if I
insert 3 rows.... I have a huge file to create with sections like this. User
may have to insert new rows. They need to all add up.

=SUM(A1:INDIRECT("A"&ROW()-1))

will work, but perhaps a more normal arrangement would be to have a
blank row between your total and the last numeric data item and
include the blank row in your Sum range. That way you can always
insert a new row in the blank row and have the range expand
automatically.
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Roger Govier]

Hi Cris

Providing the insertion is made between rows 1 and 14, the formula will
expand automatically to include the widened range.
If the user choose to highlight row 15, then the formula will not adjust.
There are numerous ways of overcoming this.
You could put your formula on row 16 and make it =SUM(A1:A15) and in row
15 Format>Cells>Alignment>Horizontal>Fill.
Typing an = sign in the cell will fill it with ======== to indicate that
this is the row being totaled to.

Regards

Roger Govier

 

[Richard Buttrey]

If you goto Tools>Options>Edit and check the Extend list formats and
formulas box, it should work automatically..

Thanks Bob, That's one I hadn't spotted although it's selected in my
Excel application.

One problem is that while it extends the formats of rows above, it
won't extend any formulae. Am I missing something?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Peo Sjoblom]

One way

=SUM(OFFSET(INDIRECT("A1"),,,COUNT(A:A)-1,))

this assumes there are no numbers below the sum formula




Regards,

Peo Sjoblom

 

[Roger Govier]

Hi

If the user choose to highlight row 15, then the formula will not adjust.

should have read
If the user chose to highlight row 15, and inserted at this point, then
the formula will not adjust.

Regards

Roger Govier

 

[Bob Phillips]

Hi Richard,

I am not sure exactly what you refer to. Could you clarify for me?

Bob

 

[Richard Buttrey]

Hi Bob,

Yes, I have A1:A10 populated with some data and formatted say Red.
B1:B10 has formulae =A1 etc.

A11 has =Sum(A1:A10), copied into B11.

I understood your reply to indicate that introducing a new row at A11,
with the "Extend List Formats & Formulas" ticked, would copy the
formatting and formulae from the row above.

The red formatting certainly gets copied, but not the formula in B10.

Rgds

Hi Richard,

I am not sure exactly what you refer to. Could you clarify for me?

Bob

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Bob Phillips]

Roger,

What I meant was that if you enter a row above B11, and then enter a value
in the new B11, the old B11 (now B12 - this is getting confusing )
automatically updates to reflect that row.

Odd thing is, it doesn't work with formulae directly, in the instance you
state, it updates by adding a value in A11. If the formula in B1 was =C1+D1
etc., it updates when the values are added to C11 AND D11.

Regards

Bob