Client statement with aging

[tomcott]

Hi All

I am trying to create a client statement for our company that has an aging at the bottom.

We would like to have monthly statements that calculates aging throughout the year.

Basically, we would like the aging from the previous months (balance brought forward) be added to the later months statements if not paid. I would think one would be able to use the invoice number as a lookup when allocating payment to cancel out the invoice in the previous months?

Please can someone assist with looking over the attached.

[JeteMc]

Hello tomcott and Welcome to Excel Forum.
Although I am sure that there is more work needed, I feel that keeping the statement on one sheet as modeled on the March sheet in the attached file will be an easier way to keep up with aging.
The table in column H:P is a proper data set of records where the Total, Aging and InvBal columns are populated using formulas.
In rows 17:39 of the statement are populated using: =IFERROR(INDEX(H$2:H$5,AGGREGATE(15,6,(ROW(H$2:H$5)-ROW(H$1))/($H$2:$H$5>EOMONTH($G$4,-1))/($H$2:$H$5<=$G$4),ROWS($A$1:$A1))),"")
The aging row of the statement is populated using: =SUMIFS($L$2:$L$5,$O$2:$O$5,">"&B50,$O$2:$O$5,"<="&A50)
Note that row 50 contains values that are placed out of sight but could be moved further down and/or hidden if needed.
Note: Change the date in cell G4 to see how the statement works.
Let us know if you have any questions.