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?

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.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Yossy" wrote:

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?

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

1 Month Books Pencils Total Grand Total
2 Jan____50____10____60____60
3 Feb____29____11____40____100
4 Mar____25____25____50____150

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

That formula copies down and requires no stress.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46116

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.

"Shane Devenshire" wrote:

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.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Yossy" wrote:

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?

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

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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46116