Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Invoice Aging

Posted on 2011-02-22
Medium Priority
805 Views
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
0
Question by:Mehram
• 3
• 3

LVL 81

Expert Comment

ID: 34958628
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

Author Comment

ID: 34958673
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

LVL 81

Expert Comment

ID: 34958689
Will a macro be acceptable?

Kevin
0

Author Comment

ID: 34958732
Can I see example file?
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 34958738
A macro solution is attached.

Kevin
Q-26841131.xls
0

Author Comment

ID: 34958774
Great one.. Cheers
Post if any other solution is available for future reference/enhancement.
Rgds.
Mehram
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month10 days, 22 hours left to enroll