Formula Help

[Yossy]

How do I make a formula automatically pick cells that are not in columns
accordingly whenever new columns are inserted.

E.G 1. Lets assume Col A,B,C is January and E,F,G is February

1 Col A B C E F G H
2 Books Pencil Total Books Pencil Total Grand Total
3 50 10 60 29 11 40 =g3+e3 (100)

Whenever I insert new column (e.g for March) it doesn't automatically affect
my grand total, I have to constantly go through the cells to add the new
Total for march into the grand total. Is there a way (formula) to allow
automatic summing of new grand total once new column for each month are
inserted.

All help and support totally appreciated.

If I have

Col A B C Total
1 5 14 20

whenever I insert new column e.g column D with Value 20, this automatically
adds the new value to the total to make 40 but does not for columns not in
order like E.G 1 above. Can anyone help with the formula to help achieve
this?

 

[Shane Devenshire]

Hi,

One method is to leave a blank column directly before the Grand total
column, in this case column H, moving the Grand Total over to column I. Now
change the formula in I2 to read
=SUM(A2:H2)
copy this down
When you get new data select the blank column and insert as many columns as
you need, always leaving a blank column. The formula will automatically
adjust.

 

[JBeaucaire]

I have to suggest you change your layout to standard table format

1 Month Books Pencils Total Grand Tota
2 Jan____50____10____60____6
3 Feb____29____11____40____10
4 Mar____25____25____50____15

Now your totals can simply be a formula
E3 =E2+D

That formula copies down and requires no stress

 

[Yossy]

I am aware of this formula and if u read my question all the way I said the
solution you profer is not helping. My question relates to columns that are
NOT from A2:H2 but Picking column cells in between multiple column cells and
Adding them as new columns are added E.G. C3 to G3 and so on as new columns
are added.

If you can help I would be totally appreciative.

 

[JBeaucaire]

Your need to duplicate columns in a non-standard way MEANS that you wil
have to maintain your sheet manually

If you don't want to do that, use the standard table format I suggeste
above, or go ahead and setup your entire sheet for one year now so yo
can create the Grand Total formula one time with all 12 needed "total
columns included