Change of range in a formula

  • Thread starter Thread starter J Hawes
  • Start date Start date
J

J Hawes

I have a simple spreadsheet where E2 is the sum of B2:D2, 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)
 
Excel can do this automatically. You just need to switch it on fro
the settings
 
Hi
just curious: which setting do you mean? At least in my
Excel version I don't think there's such a setting?
 
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
 
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?
 
Frank,

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

Bernie
MS Excel MVP
 
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.
 
Hi Ken
I also have no chance but to use the formula approach as Excel 97 does
not support this feature and I also do not trust it in all
circumstances
 
Back
Top