Beginner question for months and subtotals

[Paul Cox]

I am new to Excel (have limited exp with Word vba). I would like t
enter a list of data where column 1 shows a cost item on the lef
followed by the 12 months of the year. Row 1 will be for net, gross
tax, date inputs. Every month should have a subtotal. The plan is t
make a master sheet which I can use again for the next year.

I'm able to do this manually by creating an extra row in th
appropriate month and then at the end of the month make my subtotal
using the autosum icon, etc.

The problem is that I never know how many costs are coming for th
month and so for the master sheet I don't know where to put rows fo
subtotals or whether it's best to make these only when I need the
(i.e. when the month is finished).

Another problem is when I have created an autosum area (for the months
in Excel and I add an extra row (because I have another cost item).
The autosum area will no longer include the last row (so I will need t
do the autosum manually or in a realtime macro to take into account th
actual rows entered for the month).

The question is:

Is this simple process best left without automation?

Or could I insert vba buttons into the sheet (to create rows or mak
subtotals)?

Or could I do create something with prompts for the user, e.g. th
sheet would remember the last entry and go to the right place, etc) ?

Thx for any tips

 

[Mark Graesser]

Paul
When you insert cells after the end of a range, Excel doesn't know to include that data in the range. If you insert cells within the range Excel will automatically update that range in any formulas. If you include an empty row, or column, in your SUM range and insert any new data in front of this empty cell, the formulas will update automatically

If you want to prevent the blank cell from being used you could use Data>Validation with Allow: Custom and then leave Formula empty

Good Luck
Mark Graesse
(e-mail address removed)

----- Paul Cox > wrote: ----

I am new to Excel (have limited exp with Word vba). I would like t
enter a list of data where column 1 shows a cost item on the lef
followed by the 12 months of the year. Row 1 will be for net, gross
tax, date inputs. Every month should have a subtotal. The plan is t
make a master sheet which I can use again for the next year

I'm able to do this manually by creating an extra row in th
appropriate month and then at the end of the month make my subtotal
using the autosum icon, etc

The problem is that I never know how many costs are coming for th
month and so for the master sheet I don't know where to put rows fo
subtotals or whether it's best to make these only when I need the
(i.e. when the month is finished)

Another problem is when I have created an autosum area (for the months
in Excel and I add an extra row (because I have another cost item).
The autosum area will no longer include the last row (so I will need t
do the autosum manually or in a realtime macro to take into account th
actual rows entered for the month)

The question is:

Is this simple process best left without automation

Or could I insert vba buttons into the sheet (to create rows or mak
subtotals)

Or could I do create something with prompts for the user, e.g. th
sheet would remember the last entry and go to the right place, etc)

Thx for any tips