I have a simple spreadsheet where E2 is the sum of B22, and so forth for
each row.

If I insert a column after D2, so that F2 should now be the sum of B2:E2,
what is the proper formula that will automatically adjust to the the new
range?

Many thanks for your help.
Joan

One way to set-up for auto-adjust is
to include 1 extra blank column in the SUM()

e.g. put instead in F2: =SUM(B2:E2)
where col E is the extra blank column

Now when you insert a new column within the range
the formula will auto-adjust itself

Say, you insert a new column
by selecting E2, then Insert > Columns

The formula will adjust to,
in G2 now: =SUM(B2:F2)
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"J Hawes" <(E-Mail Removed)> wrote in message
news:iCa4d.69600$KU5.938@edtnps89...
> I have a simple spreadsheet where E2 is the sum of B22, and so forth for
> each row.
>
> If I insert a column after D2, so that F2 should now be the sum of B2:E2,
> what is the proper formula that will automatically adjust to the the new
> range?
>
> Many thanks for your help.
> Joan
>
>

Hi
try the following in E2:
=SUM($B$2:OFFSET(E2,0,-1))

>-----Original Message-----
>I have a simple spreadsheet where E2 is the sum of B22,
and so forth for
>each row.
>
>If I insert a column after D2, so that F2 should now be
the sum of B2:E2,
>what is the proper formula that will automatically adjust
to the the new
>range?
>
>Many thanks for your help.
>Joan
>
>
>.
>

Excel can do this automatically. You just need to switch it on fro
the settings
--------
Message sent via www.excelforums.com

Hi
just curious: which setting do you mean? At least in my
Excel version I don't think there's such a setting?

>-----Original Message-----
>Excel can do this automatically. You just need to switch
it on from
>the settings.
>---------
>Message sent via www.excelforums.com
>.
>

Frank,

Tools | Options.... Edit Tab, check "Extend list formats and formulas"

I forget which version this appeared in, but I think it was XL2000.
Certainly in XL XP.

HTH,
Bernie
MS Excel MVP

"Frank Kabel" <(E-Mail Removed)> wrote in message
news:0c9801c4a0c2$df237900$(E-Mail Removed)...
> Hi
> just curious: which setting do you mean? At least in my
> Excel version I don't think there's such a setting?
>
> >-----Original Message-----
> >Excel can do this automatically. You just need to switch
> it on from
> >the settings.
> >---------
> >Message sent via www.excelforums.com
> >.
> >

Hi Bernie
have to check this this evening at home :-) Excel 97 does
not have this option.

But I thought this is used if you insert new data in the
last row (column). But in this case the OP wants to insert
a new column before the existing formula column and I
doubt that this setting will change this sum formula
correctly?



>-----Original Message-----
>Frank,
>
>Tools | Options.... Edit Tab, check "Extend list formats
and formulas"
>
>I forget which version this appeared in, but I think it
was XL2000.
>Certainly in XL XP.
>
>HTH,
>Bernie
>MS Excel MVP
>
>"Frank Kabel" <(E-Mail Removed)> wrote in message
>news:0c9801c4a0c2$df237900$(E-Mail Removed)...
>> Hi
>> just curious: which setting do you mean? At least in my
>> Excel version I don't think there's such a setting?
>>
>> >-----Original Message-----
>> >Excel can do this automatically. You just need to
switch
>> it on from
>> >the settings.
>> >---------
>> >Message sent via www.excelforums.com
>> >.
>> >
>
>
>.
>

Frank,

It does change it correctly, but only when valid data is entered into the
newly inserted cell/column.

Bernie
MS Excel MVP

"Frank Kabel" <(E-Mail Removed)> wrote in message
news:3de001c4a0c5$ade27f00$(E-Mail Removed)...
> Hi Bernie
> have to check this this evening at home :-) Excel 97 does
> not have this option.
>
> But I thought this is used if you insert new data in the
> last row (column). But in this case the OP wants to insert
> a new column before the existing formula column and I
> doubt that this setting will change this sum formula
> correctly?
>
>
>
> >-----Original Message-----
> >Frank,
> >
> >Tools | Options.... Edit Tab, check "Extend list formats
> and formulas"
> >
> >I forget which version this appeared in, but I think it
> was XL2000.
> >Certainly in XL XP.
> >
> >HTH,
> >Bernie
> >MS Excel MVP
> >
> >"Frank Kabel" <(E-Mail Removed)> wrote in message
> >news:0c9801c4a0c2$df237900$(E-Mail Removed)...
> >> Hi
> >> just curious: which setting do you mean? At least in my
> >> Excel version I don't think there's such a setting?
> >>
> >> >-----Original Message-----
> >> >Excel can do this automatically. You just need to
> switch
> >> it on from
> >> >the settings.
> >> >---------
> >> >Message sent via www.excelforums.com
> >> >.
> >> >
> >
> >
> >.
> >

Hi Bernie
just tested it with Excel 2003 and yes it works :-)
Thanks for the info

--
Regards
Frank Kabel
Frankfurt, Germany

"Bernie Deitrick" <deitbe @ consumer dot org> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> Frank,
>
> It does change it correctly, but only when valid data is entered into
the
> newly inserted cell/column.
>
> Bernie
> MS Excel MVP
>
> "Frank Kabel" <(E-Mail Removed)> wrote in message
> news:3de001c4a0c5$ade27f00$(E-Mail Removed)...
> > Hi Bernie
> > have to check this this evening at home :-) Excel 97 does
> > not have this option.
> >
> > But I thought this is used if you insert new data in the
> > last row (column). But in this case the OP wants to insert
> > a new column before the existing formula column and I
> > doubt that this setting will change this sum formula
> > correctly?
> >
> >
> >
> > >-----Original Message-----
> > >Frank,
> > >
> > >Tools | Options.... Edit Tab, check "Extend list formats
> > and formulas"
> > >
> > >I forget which version this appeared in, but I think it
> > was XL2000.
> > >Certainly in XL XP.
> > >
> > >HTH,
> > >Bernie
> > >MS Excel MVP
> > >
> > >"Frank Kabel" <(E-Mail Removed)> wrote in message
> > >news:0c9801c4a0c2$df237900$(E-Mail Removed)...
> > >> Hi
> > >> just curious: which setting do you mean? At least in my
> > >> Excel version I don't think there's such a setting?
> > >>
> > >> >-----Original Message-----
> > >> >Excel can do this automatically. You just need to
> > switch
> > >> it on from
> > >> >the settings.
> > >> >---------
> > >> >Message sent via www.excelforums.com
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>
>

I still never trust it though, and always use your original approach Frank.
Belt and Braces for me thank you very much. Can't beat the extra rows/columns
etc, flag them up with a different colour to signify that they border the data
and you won't go far wrong.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Bernie
> just tested it with Excel 2003 and yes it works :-)
> Thanks for the info
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Bernie Deitrick" <deitbe @ consumer dot org> schrieb im Newsbeitrag
> news:(E-Mail Removed)...
>> Frank,
>>
>> It does change it correctly, but only when valid data is entered into
> the
>> newly inserted cell/column.
>>
>> Bernie
>> MS Excel MVP
>>
>> "Frank Kabel" <(E-Mail Removed)> wrote in message
>> news:3de001c4a0c5$ade27f00$(E-Mail Removed)...
>> > Hi Bernie
>> > have to check this this evening at home :-) Excel 97 does
>> > not have this option.
>> >
>> > But I thought this is used if you insert new data in the
>> > last row (column). But in this case the OP wants to insert
>> > a new column before the existing formula column and I
>> > doubt that this setting will change this sum formula
>> > correctly?
>> >
>> >
>> >
>> > >-----Original Message-----
>> > >Frank,
>> > >
>> > >Tools | Options.... Edit Tab, check "Extend list formats
>> > and formulas"
>> > >
>> > >I forget which version this appeared in, but I think it
>> > was XL2000.
>> > >Certainly in XL XP.
>> > >
>> > >HTH,
>> > >Bernie
>> > >MS Excel MVP
>> > >
>> > >"Frank Kabel" <(E-Mail Removed)> wrote in message
>> > >news:0c9801c4a0c2$df237900$(E-Mail Removed)...
>> > >> Hi
>> > >> just curious: which setting do you mean? At least in my
>> > >> Excel version I don't think there's such a setting?
>> > >>
>> > >> >-----Original Message-----
>> > >> >Excel can do this automatically. You just need to
>> > switch
>> > >> it on from
>> > >> >the settings.
>> > >> >---------
>> > >> >Message sent via www.excelforums.com
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.766 / Virus Database: 513 - Release Date: 17/09/2004