Firstly, what a great find this forum is - thank you in advance to all that will help! It seems I've bitten off more than I can chew with this project!
Our company is invoiced by third party suppliers, and we then have to charge these costs on to various companies within the group of companies. All costs that come in are plotted on an Excel sheet, a separate row for each cost (supplier, invoice number, description etc.) with the amount of the invoice plotted in a corresponding column based on which company we intend to charge these costs onto.
Monthly we manually prepare invoices for each of the companies we charge these costs onto. This invoices a lot of filtering, copying, pasting and formatting. We intend to use Excel to automate this process.
You can refer to the attached spreadsheet for an example of the reconciliation sheet that holds all the costs, and a sample invoice.
http://www.lagrandemoda.co.uk/Test.xls
As you can see, the costs are added to the reconciliation sheet in date order. When they are copied over to the invoice, we filter by whatever company we're invoicing (e.g. Company 123, Company ABC.) and then filter by supplier to pick up each cost in alpabetical order. This way, the invoice is prepared in alphabetical order, with each supplier as a sub-heading on the invoice, followed by all costs incurred by this supplier. If a row has a "H" or a "D" in the hold column, this invoice cannot be charged on and should not be copied across to the invoice.
This sequence is then repeated for each company in each column.
Can this be automated?
Your hints, tips, discussion, codes etc. would be much appreciated.
Alton
Our company is invoiced by third party suppliers, and we then have to charge these costs on to various companies within the group of companies. All costs that come in are plotted on an Excel sheet, a separate row for each cost (supplier, invoice number, description etc.) with the amount of the invoice plotted in a corresponding column based on which company we intend to charge these costs onto.
Monthly we manually prepare invoices for each of the companies we charge these costs onto. This invoices a lot of filtering, copying, pasting and formatting. We intend to use Excel to automate this process.
You can refer to the attached spreadsheet for an example of the reconciliation sheet that holds all the costs, and a sample invoice.
http://www.lagrandemoda.co.uk/Test.xls
As you can see, the costs are added to the reconciliation sheet in date order. When they are copied over to the invoice, we filter by whatever company we're invoicing (e.g. Company 123, Company ABC.) and then filter by supplier to pick up each cost in alpabetical order. This way, the invoice is prepared in alphabetical order, with each supplier as a sub-heading on the invoice, followed by all costs incurred by this supplier. If a row has a "H" or a "D" in the hold column, this invoice cannot be charged on and should not be copied across to the invoice.
This sequence is then repeated for each company in each column.
Can this be automated?
Your hints, tips, discussion, codes etc. would be much appreciated.
Alton
Last edited:
