Subtotal on the fly

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

Have the user enter the invoice number for EVERY row. Then, you can use
Data-->Subtotals.
See:
http://www.officearticles.com/excel/...soft_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/...soft_excel.htm

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

"cindee" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"Anne Troy" wrote:

> Have the user enter the invoice number for EVERY row. Then, you can use
> Data-->Subtotals.
> See:
> http://www.officearticles.com/excel/...soft_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/...soft_excel.htm
>
> ************
> Hope it helps!
> Anne Troy
> www.OfficeArticles.com
> Check out the NEWsgroup stats!
> Check out: www.ExcelUserConference.com
>
> "cindee" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

Sorry, Cindee. Then perhaps you should ask in the Excel programming (VBA)
instead of here in worksheet functions.
Good luck!

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

"cindee" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Anne Troy" wrote:
>
>> Have the user enter the invoice number for EVERY row. Then, you can use
>> Data-->Subtotals.
>> See:
>> http://www.officearticles.com/excel/...soft_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/...soft_excel.htm
>>
>> ************
>> Hope it helps!
>> Anne Troy
>> www.OfficeArticles.com
>> Check out the NEWsgroup stats!
>> Check out: www.ExcelUserConference.com
>>
>> "cindee" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>>
>>
>>