Currently, we use an Excel template to write invoices (a modified Excel 2010 template downloaded from the Microsoft templates' site, as I haven't got a clue how to create such a thing myself). Everything is being input manually: all client data, all project definition data, all services rendered; only the prices are calculated automatically, which is redundant because they are calculated in the project spreadsheet already :-)
We would have the following resources:
- a spreadsheet containing all client contact data (no problem to set it up, have done that before)
- a spreadsheet containing services/prices (again, no problem to set up) relative to clients (different prices for different clients)
- a spreadsheet containing project infos; among others it uses dropdown lists to fill in fields for project client info, services/prices, calculates the totals for the services rendered and also a project total; once the project is finished, a marker is put in one field to indicate that a project is ready for billing.
Up to very recently, I would run a query that put copy projects ready for billing into a new spreadsheet (this does not include all the data that needs to be on the invoice, such as client address etc, which has to be looked up; and it only contains the summary data, not the individual services, which have to be looked up).
Also, the invoice number was created manually. We had to change the format, however, and now it is more complicated: ClientCodeProjectManagerCode - YYYYMMDDserielnumberperday. From there we would open a Word template and manually insert all this data manually.
I need to automate this process as much as possible:
- first the creation of invoices (this is where the invoice number is created (manually, )) without having to enter everything again,
- then adding the invoice number to the list created by the query (this would become the 'projects invoiced' or "invoices" sheet (there is a problem however as in this sheet / list created by the query lines are not added at the bottom but content is overwritten now ( I don't know how to change that but would assume that it is just a simple check box that i forgot to tick)
This is roughly what I'm trying to achieve. I know Word and MailMerge is an option but if possible I'd rather stay within Excel.
Is this possible at all? And what would I have to pay attention to when setting up the "source" spreadsheets?
Many thanks. Any help is greatly appreciated.
Anybody who may have at least a rough idea or suggestion as to how to achieve this?
I guess I will have to continue with the manual process then.