If you are using Excel 2003, then it has a built-in feature for this.
Highlight your existing dataset (including header row) and select
Data->List->Create List. This converts your data into a "database" type of
design with independent records (rows) and a designated place to add new
records (indicated at the end of your dataset by a blue asterisk). This
also converts your headers into functional sort/filter tools. Also, when
you add new records, all formatting and formulae from the row above carry
down to the new record. If copying and pasting from another source,
multiple records can be added at once.
Now if you go back to Data->List->Toggle Total Row, it will put a total row
at the bottom of the current list. Every column has a drop down arrow to
select what kind of total you want for that column (count, sum, average,
nothing at all) and you just select the one you want...no need to remember
the syntax of the SUBTOTAL formula. This function also automatically moves
the total row down when new records are added so that the totals are always
at the end of the list.
If this does not help you in your situation, another option is to highlight
all of your data (including headers) and an additional number of available
empty rows after your existing data to accommodate what you think might be
needed, then apply an AutoFilter. Make sure all formulas and formatting are
carried down to these extra empty rows. Let's say you ended at row 1000.
Format the next row (1001) to be just 4 points high, then put your totals
row after that (1002). You'll have to manually put in your SUM, AVERAGE,
COUNT, OR SUBTOTAL formulas into that row. Now you have lots of room to add
new records, and when you need to see the report without all those blank
rows, simply go to the first header and select "NonBlanks." All of the
blank rows are temporarily hidden and the totals row is at the bottom
directly underneath the data...the blank buffer row (1001) protects the
totals from accidentally being incorporated into the sorting/filtering.
When done with viewing/printing your report, tell that filter to "Show All"
and your back to data entry mode with all of those blank rows ready for new
records.
If you need a more automated solution than that, you'll have to create some
VBA code, but hopefully one of these two ideas will meet your need, as they
are both much simpler than creating code.
- KC
