Totalling columns and repeating formulas in new entries

  • Thread starter Thread starter seanof30306
  • Start date Start date
S

seanof30306

i'm working in excel 2003

i've tried searching this, but have come up with nothing. it seems s
basic, i'm sure i must be calling it the wrong thing or something.

in my worksheet (that's what a spreadsheet is called now, isn't it?)
enter expenses in column "e" and revenues in column "i", with the ne
gain or loss for that row (entry) showing up in column "j". i had n
problem setting that up.

the first problem i'm having is the formulas and formatting bein
copied to the next entry. some of the new entries have no value i
column "e", column "i" (or both). after one of those entries wit
missing values, the next entry will not copy the formulas or formattin
from the last entry with data. I'm assuming the "3 entries" in a ro
format has been broken. is there a way to make the formulas an
formatting in each row default to each new entry?

the second problem i'm having is sorting. entries are sorted first b
date, then by time. there are no other sorts. some of my entries ar
put in out of sequence. i'd like the worksheet to automatically sor
each new entry as it's entered. is this possible?

the third problem is totals. try as i might, i cannot figure out ho
to make the bottom row show totals of the columns. i'd like the las
cell in columns "e", "i" and "j" to show the totals of each respectiv
column. i'd also like this "totals" row to stay below the last entr
in the workbook as new entries are made.

finally, i'd like a "monthy subtotals" row to embed in the workboo
immediately after the last day of each month and remain there.

can anyone help me?

thank
 
Instead of depending on the Auto Extend feature, you could use Excel
built in data form (Data>Form). It will fill in all the formulas for
you. Or download John Walkenbach's Enhanced Data Form. It's a free
add-in, allows combo boxes, inserts new rows with formulas, and has
unlimited fields.

http://j-walk.com/ss/dataform/index.htm

An autosort would only be possible if you add macros to the workbook.
Instead, you could sort the list after you've finished the batch of new
entries.

Define your table as a list (Data>List>Create List)
Select a cell two rows down from the end of the list
Enter a SUBTOTAL function in each column that you want to total.
For example: =SUBTOTAL(9,E2:E200)
Add new rows to the end of the list, and the Totals will automatically
move down.

For monthly subtotals, you could insert a row at the end of each month's
entries.
Use the SUBTOTAL function to sum the entries for the month, e.g.:
=SUBTOTAL(9,E2:E20)
 
Back
Top