Excel 2007 : help in creating an invoice

**tetrisbrokeme** · 10-29-2009, 04:47 AM

hi

i am currently keeping a track of sales made by my step dad's company using a table in excel. what he needs now though is for me to create invoices for him, and i figured it would be quickest (as he'll be invoicing about 15 companies on a repeat basis) if there was i way that i could automatically copy some of the data from worksheet 1 into a pre-laid out invoice on another worksheet. can anyone help at all????? i'm struggling to word this correctly so if you need more info you can add me on msn and i can send you the data.....

**Richard Buttrey** · 10-29-2009, 05:09 AM

Hi,

It depends to some extent on whether you want a summarised invoice for each customer per month or an invoice for each sale item. I'll assume the latter for the moment.

Assuming the table is in a pukka database format, i.e. one row for for each sale, and that one of the column fields contains a unique Invoice number reference, the approach I'd adopt is to set out your invoice proforma with cells for the information from the various columns of the database. One of the cells will be the unique Invoice reference. Name that cell say 'InvNo'

In that unique invoice cell enter one of the invoice numbers. Now for all the other cells use a formula like the following. For example one of the cells will be a 'date' cell. Assuming the database is on Sheet 1 and column B contains the date and column A the unique invoice reference:

=INDEX(Sheet1!B:B,MATCH(InvNo,Sheet1!A:A,False),1)

follow the same approach for all the other cells required by the invoice.

When you have this working the next step would be to automate the whole process so that you could set a macro running which would change the invoice reference and print the invoices one after another - but first things first.

HTH

**tetrisbrokeme** · 10-29-2009, 06:02 AM

Hi Richard

Many thanks for your swift response..... I have to admit my knowledge of Excel is somewhat limited, and Macro is definitely NOT a strong point (probably weaker than a weak point!!!)

Anyway, this is a better idea of what I am trying to achieve:

On worksheet 1 I have a table that has Company Name, Product Number, My Commission Rate, Estimated Annual Usage, Supplier Name, Date Sold, Date Expected. Worksheet 2 has my data for the drop down lists I use for both Commission Rate and Supplier. I want Worksheet 3 to be my invoice template, and so when I click to invoice a supplier from Worksheet 1 it recognises the supplier name and automatically fills in the details for the Company Name, Product Number, My Commission Rate, Estimated Annual Usage. Is this possible??? The alternative would be to have numerous worksheets, one for each supplier, and when I click the drop down on worksheet 1 and select the supplier for that particular customer, it copies it across to the invoice. That sounds easier in my head..... but you're most definitely the expert on this one!

If you want to talk further on this, you can msn me: you_knows_it_spa@hotmail.com

I really appreciate your time!

**suzzy** · 10-29-2009, 06:32 AM

Hi tetrisbrokeme,

The spreadsheet template below may be of help. It is a flexible invoice template.

http://spreadsheetzone.com/templateview.aspx?i=17

Suzzy

**Richard Buttrey** · 10-29-2009, 07:01 AM

Hi,

See the attached which will hopefully give you some ideas.

Rgds

**tetrisbrokeme** · 10-29-2009, 07:40 AM

Hi Richard

Many thanks for this! It has certainly given me an idea of how to work it out. I just need to play around with it a bit now I think. One thing I wondered about though.... I've decided to create a different tab worksheet for each supplier. Is there a way for the data to auto fill in to the correct worksheet once i select the correct supplier on worksheet 1?