How to transfer info automatically from one excel spreadsheet to another eg invoices

Slideshowbob

ok

so i have a summary spreadsheet of invoices, with:

Invoice number, Date, Company Name, Contact Name, Product, Quantity, Rate on each line.

Also have template invoice spreadsheet.

Currently manually inputing info into individual invoice spreadsheets 1 at a time and saving them as the invoice number.

Is there a formula / way of doing this automatically? If so, how is it done?

Or is it easier to do it other way round eg populate invoice then send info automatically to invoice summary spreadsheet?

Thanks

JonathanAnon

I have my own invoicing system running in Microsoft Access. I have a flat table for each record (i.e. each separate job) which has fields for invoicenumber (generated automatically by Access depending on what numbers are already used), date, cost, VAT rate, hours on job, customer details, date of issue, etc etc..... Once I have all this information entered through an Access form, I click a button to open up an Access form with all of the job information laid out properly.

Most people use something like TASBooks, but Access is more configurable if you are prepared to work at it.

Another alternative would be to create an Excel template file containing all the basic information, and just adding the details yourself... This would be more convenient than resaving an existing invoice.

Thoie

If I was you I'd move the invoice template to Word and read up on Mail Merge. http://www.meadinkent.co.uk/mail-merge1.htm

Basically if your invoice# in the spreadsheet is in the column "InvoiceNo", then in Word while setting up the mail merge you'd just once put in {filename/InvoiceNo} (a wizard will do it for you ).

Then when you're ready to generate the invoices, you can hit "merge" and all the details will fill in automatically. You can then manually save each page as a file with the invoice number, or, if you read up on macros, make Word do the saving for you.

JonathanAnon

Thoie wrote: »

If I was you I'd move the invoice tempalte to Word and read up on Mail Merge. http://www.meadinkent.co.uk/mail-merge1.htm

I would only suggest approaching it this way if you did your invoices periodically (i.e. one a month).. Mail merges are usually more appropriate for mailshots. If you just invoice every day after the job is done so that you dont forget (like I do), then this is not practical..

Thoie

JonathanAnon wrote: »

I would only suggest approaching it this way if you did your invoices periodically (i.e. one a month).. Mail merges are usually more appropriate for mailshots. If you just invoice every day after the job is done so that you dont forget (like I do), then this is not practical..

True, I was thinking of monthly billing, and keeping track of due date and paid date on each line as well, so that it would automatically do reminders of unpaid invoices as well.

If (s)he's doing an invoice each time, then I'd be inclined to go with the idea of filling in each invoice separately, and then once a day/week/whatever collating the invoices into a single sheet by creating a macro to read the data from all the files in a particular folder. Here's a similar solution:
http://en.allexperts.com/q/Excel-1059/Combining-data-multiple-workbooks.htm

JonathanAnon

Just thinking back, I initially had an Excel file with a few forumlae (date, vat rates etc etc).... I then found the equivalent formulae in Word and moved the invoices to word... only problem with this is that the formulae DO NOT auto update, I had to press F9 I think to update the formulae when I would change a number.

I saved the documents as 010.doc, 011.doc etc so that it would be easy to work out the next invoice number. It was a bit cumbersome, but by doing out the first forty or so invoices, I was able to work out what tables, fields, forms etc etc I would need to have in Access.

Thoie

Meanwhile, Sideshowbob is sensibly out getting drunk, while we're sitting here with a glass of wine debating the best way to run his business

The ideal solution would be something like TAS or Access, but if he's stuck with just Excel, and as the wine left in the bottle decreases, I think maybe a new invoice on each tab, autonumbering the tabs, and then collating from that would be best.

JonathanAnon

Or as the wine (and recession aggression) kicks in, you say "I dont know why you're bothering sending an invoice, the b*stard probably wont pay it anyway. Probably declare himself bankrupt, the fupn c**t"...