pushing data from master monthly sales sheet to debtors list

Hello,

I am new to this site and was hoping someone could help with a problem.

I am working with a master sales sheet where all sales activity and cash flow is recorded in a different sheet for every month. Unfortunately we have a debtors list which is currently being updated manually. I want to use Excel so that once the sales activity and cashflow is updated in that sheet then the debtors list is automatically updated. So I need a formula/Macro to recognise that there is money owing and how much and record this in the debtors list and then once the monies have been paid off that the debtors list will be updated and the invoice will be removed from the debtors list.

Please help as I have been searching the internet for a solution with no joy.

Thanks,
Anne

Please see an example of the sales cash flow spreadsheet and the debtors spreadsheet that we are currently using and a sample debtors list that I would hope to create from the live upload on the sales spreadsheet- I would like if a debtors list would automatically create itself from this and if a customer pays their account that the debtors list is automatically cleared.

Thanks,
Anne

On the sales sheet I want the data by row to automatically transfer to the debtors sheet if column F which is marked 'outstanding'shows up a value if column F shows up 0 then I do not want it to push to the debtors sheet.I do not want to delete anything in the sales sheet as that is where we record all our sales.

The Debtors list;
I want the Debt to be seen on this page until it is paid off and once the debt is paid off and outstanding column shows zero then I want it to automatically delete.

Hope this helps you and you can help me make this possible.

Anne

Hi Jeff,

Thanks for you time spent when I try to run the Macro It tells me that there is a bug? jsut wondering what I need to have open or saved the debtors file anywhere in particular should I save the sales cashflow xls somewhere in particular?

Anne

Hi Jeff,

Thanks for this - it is almost perfect.

I want to input all data in the sales spreadsheet and for it always to stay there, regardless of the amount paid or outstanding, Is this possible?

Because when I update the the sales spreadsheet and an account is paid up to date I want it to then be removed off the debtors list automatically so I only want to work and upload data on the sales spreadsheet and then at the end of the year/month I should have a full history of all sales for that month and then the debtors list will be a snapshot of monies outstanding at any giving time.
I am getting excited about this working for my company - and I am grateful for all your help.

Anne

This is exactly what I need the spreadsheet to do. Now the reason why I cannot do what you suggested;

There has been a system in place for years here where each month gets a new sheet for sales so at the end of each month a sales total is calculated and then left on the system so the accounts and sales people can see what the sales were in a given month and where they went. This is the way everyone works and they do not want to change from this.


Debtors list will contain eveyone that owes money and as invoices can sometimes lapse for more than a month like for example debt could be in place for 3 months or more and the second spreadsheet is just there for anyone from accounts and sales to see straight off who owes money with out having to thrawl back over months of sheets!

I am only new to this business and this is how it has been done here for years so that is how they want to keep it.

Currently everything is updated manually and they might as well just draw a table in a word document rather than using excel as everything is typed in automatically. I am looking for excel to do some of the work for us here. If you can work it that the sales remain in the spreadsheet I think the macro that you have would work a treat.

Thanks,
Anne