D
dmadore
I am attempting to streamline an aging report that I've exported from
Quickbooks. What I've written macros to do so far is:
1. Basic sort functions and deleting columns that are useless.
2. Insert 3 blank lines below groups of text, i.e. if there are 50
open invoices from one customer it puts 3 blank lines below the last
instance of the customer name.
3. I've gotten rid of all of the repeat data so that the customer name
doesn't appear in the colum for each invoice.
What I'd like to do now is use the top blank row I've inserted after
each customer and put the following headings in: Current, 30 Days, 60
Days, 90+ Days, Total.
Beneath each of these headings I would like to put the following
formulas, in respective order:
=SUMIF($K$2:$K$47,0,$M$2:$M$47)
=SUMIF($K$2:$K$46,"<="&(30),$M$2:$M$46)-SUMIF($K$2:$K$46,"<"&(1),$M$2:$M$46)
=SUMIF($K$2:$K$46,"<="&(60),$M$2:$M$46)-SUMIF($K$2:$K$46,"<"&(30),$M$2:$M$46)
and so on.
Data in column k represents the days aged, and column m is the amount
still outstanding. Normally these would not be absolute references.
There is no set number of invoices per customer so I can't set it to
repeat every x number of rows. Ideally what I would like to do is type
the data in the first and second blank rows and then have a macro that
will repeat this data in each blank set. However, I would like to have
the calculations done on each group. Is this possible? I'm a macro
novice so any help would be appreciated.
Dawn
Quickbooks. What I've written macros to do so far is:
1. Basic sort functions and deleting columns that are useless.
2. Insert 3 blank lines below groups of text, i.e. if there are 50
open invoices from one customer it puts 3 blank lines below the last
instance of the customer name.
3. I've gotten rid of all of the repeat data so that the customer name
doesn't appear in the colum for each invoice.
What I'd like to do now is use the top blank row I've inserted after
each customer and put the following headings in: Current, 30 Days, 60
Days, 90+ Days, Total.
Beneath each of these headings I would like to put the following
formulas, in respective order:
=SUMIF($K$2:$K$47,0,$M$2:$M$47)
=SUMIF($K$2:$K$46,"<="&(30),$M$2:$M$46)-SUMIF($K$2:$K$46,"<"&(1),$M$2:$M$46)
=SUMIF($K$2:$K$46,"<="&(60),$M$2:$M$46)-SUMIF($K$2:$K$46,"<"&(30),$M$2:$M$46)
and so on.
Data in column k represents the days aged, and column m is the amount
still outstanding. Normally these would not be absolute references.
There is no set number of invoices per customer so I can't set it to
repeat every x number of rows. Ideally what I would like to do is type
the data in the first and second blank rows and then have a macro that
will repeat this data in each blank set. However, I would like to have
the calculations done on each group. Is this possible? I'm a macro
novice so any help would be appreciated.
Dawn