Create Invoices That Auto Update Excel Spreadsheet

PJB

Hi All

At present I use a word document with an Excel Workbook and have to manually cut and paste from this to the Excel spreadsheet, but I want to create an invoice template where I need Invoice no (automatically generated if possible), Date, Client, Fee, Mileage, Disbursements and Total and when I complete an invoice this data will automatically update onto a spreadsheet containing all invoices in a list.

Does anybody know if this is possible and how I do it please?

Thanks

InsideInsurance

That would sound like a basic accounting package of which there are many including free/ opensource varieties.

If you want to do it yourself you would normally do it the other way round in terms of entering the data into a database (or spreadsheet if you prefer) and then pulling that through into a template for the invoice

PJB

Thanks InsidInsurance

I think you are right about going from Excel to Word/Invoice, but how do I do that?

gonzo127

access database would probably be my initial idea if its invoices for specific things because

access will allow you to create a 'form' which can be designed to look like a invoice, and so printed as such, and as you input the information it will automatically be stored in cells in the 'background' meaning you can export the information should you wish into excel.

on the flip side you could use excel in which you input the information into your spreadsheet 'backing sheet', but having designed a seperate tab for your invoice, which using dropdowns and lookups will allow you to select the details of the person you are invoicing and automatically pull through the details from the backing sheet,

InsideInsurance

Not an expert as I would do it from a different route but imagine you can do it using Words' mail merge for one and Insert->Field allows you to hook up to an XLS too

EchoLocation

InsideInsurance wrote: »

Not an expert as I would do it from a different route but imagine you can do it using Words' mail merge for one and Insert->Field allows you to hook up to an XLS too

Yes, if you want to maintain the Word/Excel partnership then Mail Merge is the best way.

PJB

Hi All, thanks for all your advice so far!

Been away, so not monitored recently.

I do not have to keep the Word/Excel combo but maybe better the devil you know!

I have created drop down menus for the companies and contacts etc using mail merge.

Now I just need to know how I link the cells in excel to the Word doc or vice versa.

Would like column 1 in Excel to be the Invoice ref, 2 the co, 3 date, 4 client/venue, 5 Fee, 6 Mileage, 7 disb's & 7 Total.

Been looking around and can't find anything :wall:

chris-j

Create excel workbook with columns, save this.
Create word doc and create basic structure you want, open mailmerge toolbar and select icon to open data source (the excel file). Add fields from excel file where you want the excel data to appear.
Save the file.
When you merge you can choose which merge to print (all, range or specific one)mail merge toolbar also has navigation arrows to select entry you want.
words online help has reasonable guide for doing this.

johnmc

Mail merge will work but is way OTT.

I agree with gonzo127. Use Excel only and set a tab up as the invoice, put a button on it to run a macro which pulls the data from the last entry. You can hide the grid lines and unneeded columns and rows. Use print preview to set the size to match an A4 sheet for printing.

You can make it pretty with a data entry screen with a macro to add the data at the end.

The best option is to use Access, IMO.