subtotals and formulas

A

Amber M

I would like to set-up a template to track supplies and amounts paid. Row 1
would have the job # and the various supplies while I want row 2, right under
each supply, to indicate how much that particular item costs. I will then
sort the supplies by job number. I've encountered two problems:
1). The subtotal function will not allow me to subtotal with the prices in
row 2. Anyway around this short of actually hiding row 2?
2). I want formulas already set-up on my template to calculate the grand
total of each particular supply, multiplied by cost per item. However, when I
do that, the formula is actually counting each individual number AND the
grand total of those numbers which, of course, skews my amounts. Anyway
around that?

Remember, I'm thinking quick and easy so that I can plug in my info and have
little fiddling to do.

Thanks!
 
E

Earl Kiosterud

Amber,

I think you need to set this up as a bona-fide table. Have a column for job
number, and one for supply number, and put each supply in a separate row.
You can include other columns relevant to each supply (description, amount,
etc.). The job number will repeat in each row, until it changes (you can
copy it down with the fill handle, or use Ctrl-apostrophe to repeat it as
you go. Now you're doing a for-real database. You can use a pivot table to
summarize job costs, or Data - Subtotals.
 
A

Amber M

Thanks for the info, Earl!

Earl Kiosterud said:
Amber,

I think you need to set this up as a bona-fide table. Have a column for job
number, and one for supply number, and put each supply in a separate row.
You can include other columns relevant to each supply (description, amount,
etc.). The job number will repeat in each row, until it changes (you can
copy it down with the fill handle, or use Ctrl-apostrophe to repeat it as
you go. Now you're doing a for-real database. You can use a pivot table to
summarize job costs, or Data - Subtotals.
Click to expand...
 
Top