+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : help in creating an invoice

  1. #1
    Registered User
    Join Date
    10-29-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question help in creating an invoice

    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.....

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: help in creating an invoice

    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:

    Please Login or Register  to view this content.
    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
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-29-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    3

    Exclamation Re: help in creating an invoice

    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!

  4. #4
    Registered User
    Join Date
    06-18-2009
    Location
    US
    MS-Off Ver
    Office 2003
    Posts
    23

    Re: help in creating an invoice

    Hi tetrisbrokeme,

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

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

    Suzzy

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: help in creating an invoice

    Hi,

    See the attached which will hopefully give you some ideas.

    Rgds
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-29-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: help in creating an invoice

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1