Solved

Invoice Aging

Posted on 2011-02-22
6
787 Views
Last Modified: 2012-05-11
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
Comment
Question by:Mehram
  • 3
  • 3
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:Mehram
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

by:zorvek (Kevin Jones)
ID: 34958689
Will a macro be acceptable?

Kevin
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Mehram
ID: 34958732
Can I see example file?
0
 
LVL 81

Accepted Solution

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

Kevin
Q-26841131.xls
0
 

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now