Subtotal on the fly

C

cindee

I'm creating an invoice entry sheet. I have a place for users to put the
invoice total and the various distributions on the invoice and would like
them to total to validate the Invoice total. My data must be laid out in
spreadsheet format for import to their AP system.

Currently I have it laid out like this:

INV NUM INV AMT GL ACCT DIST AMT
798997 120.00 6650 60.00
6640 50.00
6601 10.00
977-883 100.00 6098 100.00
08-00009 5,060.00 7009 1,000.00
7004 4,000.00
1050 60.00

I'd like the Distribution Column to total for each Invoice...but don't have
a way to tell it WHEN to quick totalling and start a new total. Is it
possible to write something that will do that for me? If so, then we can
have it validate the user's input for Invoice Amount against the sum of the
Distribution Amounts.

Thanks in advance,

Cindee
 
A

Anne Troy

Have the user enter the invoice number for EVERY row. Then, you can use
Data-->Subtotals.
See:
http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm
If you get the worksheets AFTER the user has entered the GL entries, then
you could use this method to fill in the invoice numbers:
http://www.officearticles.com/excel/fill_in_the_blanks_in_microsoft_excel.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com
 
C

cindee

Anne - if I wasn't concerned about appearance or user convenience, your
solution would be helpful but since I'll have a single invoice total and
75-100 distribution entries, asking them to continue to enter the same total
over and over isn't efficient. I was looking for more of a formula/VB answer
here.

These sheets will be coming from about 60 locations so there can be no
manipulation of the sheets after they are received. Again, I'm looking for a
more efficient, less user-involved answer.

Thanks

Cindee
 
Top