Accounts Receivable Tracking and Reports

[Einsteinguru]

I just upgraded to Windows 8 and my accounts receivable software is no longer supported by the operating system. I was hoping to create a workbook in Excel that can achieve the same results as my software.

I have a list of clients and I.D.'s matched to them that I have already set up into the workbook and have an area where I will be posting the invoice information for tracking purposes. What I need now is some assistance setting up the formulas necessary to automatically generate reports in the different sheets of the workbook when I add a new invoice or record that an invoice has been paid.

I need a VLookup that finds and returns multiple values and then adds them together to get a current balance report that lists each individual client and the balance of their account collectively.

Also I am looking for an aging report/ unpaid invoice report that would both list all invoices and their amounts that are unpaid and sort the first by Client, then invoice number or date. It will show the amount that remains unpaid for each invoice and how long that invoice has been outstanding by the day.

Most of these formulas I can do myself (days the invoice has been outstanding and vertical lookup to match I.D. with a name or vice versa); however what I am having trouble with is making this a live workbook and pull data from all transactions.

In a nutshell I need to be able to print a report each month with the current balances and current unpaid invoices with there amounts and aging history; I have considered having a separate workbook for each month, but how would I carry over unpaid invoices from prior months.

Thank you in advance for any help you can give, and if there is any other information needed let me know so I can answer questions to help iron out the details of my problem.

Thanks Again!

 

[Brettster]

What you are wanting sounds like a database to me (ie. MS Access).

 

[Einsteinguru]

What you are wanting sounds like a database to me (ie. MS Access).

Yes I could see how it would be fairly simple to create an access database, unfortunately we do not have access and that is why I was hoping Excel would be able to accommodate. Since there were only the two reports I had hoped this would be feasible. Thanks for getting back so quick.

 

[shyy]

If you want to pull data from other spreadsheets, I suggest you look at the INDIRECT function. Post up screen shots of your layout so people can have a better understanding of what you want.