Need open ended cell for Sum range.

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 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)


"Cris B." <Cris [email protected]> wrote in message
news:[email protected]...
> 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
[email protected]> 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 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



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

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 [email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> wrote:

>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
__________________________

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 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
>
>
>
> 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" <[email protected]> wrote in
message news:[email protected]...
> On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
> <[email protected]> wrote:
>
> >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
> __________________________

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"
<[email protected]> wrote:

>Hi Richard,
>
>I am not sure exactly what you refer to. Could you clarify for me?
>
>Bob
>
>
>"Richard Buttrey" <[email protected]> wrote in
>message news:[email protected]...
>> On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
>> <[email protected]> wrote:
>>
>> >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
>> __________________________
>

__
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" <[email protected]> wrote in
message news:[email protected]...
> 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"
> <[email protected]> wrote:
>
> >Hi Richard,
> >
> >I am not sure exactly what you refer to. Could you clarify for me?
> >
> >Bob
> >
> >
> >"Richard Buttrey" <[email protected]> wrote in
> >message news:[email protected]...
> >> On Fri, 14 Oct 2005 14:44:16 +0100, "Bob Phillips"
> >> <[email protected]> wrote:
> >>
> >> >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
> >> __________________________
> >
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________