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.

If you goto ToolsOptionsEdit and check the Extend list formats and
formulas box, it should work automatically..

--

HTH

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


"Cris B." <Cris wrote in message
...
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.

On Fri, 14 Oct 2005 06:33:16 -0700, "Cris B." <Cris
wrote:

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
__________________________

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 FormatCellsAlignmentHorizontalFill.
Typing an = sign in the cell will fill it with ======== to indicate that
this is the row being totaled to.

Regards

Roger Govier



Cris B. wrote:

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.

On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
wrote:

If you goto ToolsOptionsEdit 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
__________________________

One way

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

this assumes there are no numbers below the sum formula




Regards,

Peo Sjoblom


"Cris B." <Cris wrote in message
...
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.

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



Roger Govier wrote:

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 FormatCellsAlignmentHorizontalFill.
Typing an = sign in the cell will fill it with ======== to indicate
that this is the row being totaled to.

Regards

Roger Govier



Cris B. wrote:

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.

Hi Richard,

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

Bob


"Richard Buttrey" wrote in
message ...
On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
wrote:

If you goto ToolsOptionsEdit 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
__________________________

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

On Fri, 14 Oct 2005 15:47:55 +0100, "Bob Phillips"
wrote:

Hi Richard,

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

Bob


"Richard Buttrey" wrote in
message ...
On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
wrote:

If you goto ToolsOptionsEdit 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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

"Richard Buttrey" wrote in
message ...
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

On Fri, 14 Oct 2005 15:47:55 +0100, "Bob Phillips"
wrote:

Hi Richard,

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

Bob


"Richard Buttrey" wrote in
message ...
On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
wrote:

If you goto ToolsOptionsEdit 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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________