1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL: Create a auto number for invoicing

Discussion in 'Business Applications' started by lisabryant, Nov 8, 2000.

Thread Status:
Not open for further replies.
Advertisement
  1. lisabryant

    lisabryant Thread Starter

    Joined:
    Nov 8, 2000
    Messages:
    3
    I want to create a template for invoicing that will automatically give me the next invoice number to use when I create a new invoice. Can anyone tell me how I create a automatic number for invoicing? Or where can I can find information regarding this matter.
     
  2. Mulderator

    Mulderator

    Joined:
    Feb 20, 1999
    Messages:
    51,021
    Have you considered using Quickbooks? It's very inexpensive and does a much better job at what your trying to do (i.e., billing, collections, etc.). Anyway, Excel has an Invoice Template that you can take a look at. Just Choose File, New, Click "Spreadsheet Solutions" and choose Invoice.
     
  3. cdevaro

    cdevaro

    Joined:
    Aug 19, 1999
    Messages:
    99
    I have a similar situation and would like to know if there is any way to put a formula in a cell to issue a number that stays with a record. I am creating a database list and would like Excel to issue a number for a particular record. I have seen the invoice template in Excel, but what I need is slightly different. If there is a way to this please let me know.
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,731
    I've got an answer for you, CD, and I'm determined to get an answer for Lisa.

    CD, here's an example: Put Invoice# in cell A1, put 1 in A2.

    You don't have to do this formatting part: Click on the letter A to select the column. Hit Format-Cells-Number tab. Click Custom on the bottom left. In the skinny box, delete "general" if it appears, and type 0000 (or however many numbers you would like in your "invoice" or number scheme.

    Put your first invoice (or record of data) in Row 2. In A3, type the following formula:

    =if(isblank(b3),"",a2+1)

    Then, as soon as you type something in b3 (and it is no longer blank),it will perform the A2+1 and give you a number 2, right? Right. You may want to protect that column. Hope this helps.

    ------------------
    ~dreamboat~
    Brainbench MVP for Microsoft Word
    Brainbench
     
  5. lisabryant

    lisabryant Thread Starter

    Joined:
    Nov 8, 2000
    Messages:
    3
    I have seen this template and basicly that is what I want to be able to do. My invoices are already in Excel (small and very basic), but I would like to use my own invoice layout. Thanking you for your time.
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Mulder:
    Have you considered using Quickbooks? It's very inexpensive and does a much better job at what your trying to do (i.e., billing, collections, etc.). Anyway, Excel has an Invoice Template that you can take a look at. Just Choose File, New, Click "Spreadsheet Solutions" and choose Invoice.<HR></BLOCKQUOTE>

     
  6. cdevaro

    cdevaro

    Joined:
    Aug 19, 1999
    Messages:
    99
    Thanks for the help Dreamboat, but the problem with this is that I will have a list of products on order and from this list, once they are shipped, I will have a macro move them from products on order to a products shipped (and invoiced)list. When I move the list, I would start back from the beginning. If you can think of another way to autonumber or of a better way to set up my system, please share.
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,731
    Got your answer, Lisa. Here's the code and instructions:

    Private Sub Workbook_Open()
    With Range("A1")
    .NumberFormat = "00000"
    .Value = .Value + 1
    End With
    End Sub

    Open your invoice file or a blank workbook. Hit Alt-F11. On the left column of the visual basic editor, double-click "this worksheet". Then, on the right, paste the code above. This code is designed to put the number in A1. Just replace the A1 with the cell you want your number in.

    Then, type the number you want your invoices to start with (to test, put a 0 in cell a1). Put all your other invoice info in and save the file as a regular workbook. Close the workbook, then open it. You'll notice the number becomes number 00001. You'll have to save the workbook each time so it retains the last invoice number. If you mess up, you just don't save the file. You'll probably want to record a macro too that will wipe out all the invoice info (after printing and/or saving to another file), save and close your workbook.

    If you need some real help, just email your invoice to me. I'll get it going for you.

    ------------------
    ~dreamboat~
    Brainbench MVP for Microsoft Word
    Brainbench
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/37841