Invoice Aging

Dear Experts,
I have three columns in excel sheet1 namely:
1.      Party name
2.      Invoice Date
3.      Invoice amount
We need to get aging of invoices in sheet2 after grouping party name with the following columns.
1.      Party name
2.      1-30 days
3.      31-60 days
4.      61-90 days
5.      91-120 days
6.      Over 120 days
7.      Total
Can someone help me to do such task?
Rgds.
Mehram
MehramAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
A macro solution is attached.

Kevin
Q-26841131.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
A formula like this will work:

=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*((TODAY()-Sheet1!$B$2:$B$1000)>(COLUMN()-2)*30)*((TODAY()-Sheet1!$B$2:$B$1000)<(COLUMN()-1)*30+1)*Sheet1!$C$2:$C$1000)

See the example.

Kevin
Q-26841131.xls
0
 
MehramAuthor Commented:
This is one of great stuff, only need to get it little bit dynamic. Sheet1 information will copy past by the user always and sheet2 will dynamically update according to inform/agent name. Can we get party name by populate itself after grouping in sheet2?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
zorvek (Kevin Jones)ConsultantCommented:
Will a macro be acceptable?

Kevin
0
 
MehramAuthor Commented:
Can I see example file?
0
 
MehramAuthor Commented:
Great one.. Cheers
Post if any other solution is available for future reference/enhancement.
Rgds.
Mehram
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.