+ Reply to Thread
Results 1 to 7 of 7

How2 change an invoice number automatically and link it?

  1. #1
    Registered User
    Join Date
    06-20-2014
    Location
    Canada
    MS-Off Ver
    pintree3
    Posts
    9

    How2 change an invoice number automatically and link it?

    Sorry I'm both Excel and Accounting illiterate so don't know how to phrase words accordingly.
    I have a spreadsheet whereby I have an invoice number, written as such (without the quotes)--"2014-001". We will call this Cell ABC
    In another cell I have a time period for example "May 28-Jun 18"
    We will call this Cell XYZ
    All of this is within a template which I keep on renaming every time I create a new invoice.
    What I would like to do is the following:
    Link all files from this one template to be together, as part of a group. The way to explain this is to tell you and ask the following:
    Considering the above I would like that every time I change the entry in cell XYZ Cell ABC automatically changes to the next number (in this case it would change from 2014-001 to 2014-002
    Now what do I mean by "Link all files from this one template to be together..."?
    Assuming I am on this spreadsheet which I named 'SONY-may' with the invoice number "2014-001"
    I then go on this same spreadsheet and make the changes named above and seeing that '001 has now changed to 002 I will rename it to 'Nokia-may'
    Next time around I take 'Nokia-may' which has the invoice 002 will changing the date mentioned now make it '003'? if yes great, if no, then how do I do so.
    And now the really tricky part (The reason for the 'link' as one).
    Assuming I now have 12 spreadsheets of this one template, all with a different name and all with the correct invoice # series (001, 002, 003, ....12)
    I now take invoice 005 and make changes I would want the number NOT to go to 006 but 013--follow the last in the series. Is this possible?
    Why? I am a business of 1. My customers have their own folder and their own invoice so obviously every time I make a change I would want to change the invoice number. If I create invoice 1 and 2 and 3 for customer A and then go to customer B and create invoice 4 if I were then to go back to customer A I would be taking invoice 3 from him so it can't go to invoice 4 since customer B already has invoice 4. It would need to go to 5.
    I tried some invoicing software but don't like them--too complicated and don't serve my simple purpose.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: How2 change an invoice number automatically and link it?

    all ypu need to do is to keep a table by customer with 2 fields, customer no. and last invoice number . so when you type the customer number cell abc will seek that number based on the customer number found in the table, add one and display the correct invoice number. In addition, the table will be incremented by one.

    I would suggest that you keep one template and store in records so you could retrieve any recorded invoices by typing the invoice number

  3. #3
    Registered User
    Join Date
    06-20-2014
    Location
    Canada
    MS-Off Ver
    pintree3
    Posts
    9

    Re: How2 change an invoice number automatically and link it?

    Thanks rcm, I really appreciate it. Do note that I started with the fact that I am both ''Excel and Accounting illiterate'' which means that though your advice seems good to me I have no clue as to how to go about in doing it.

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: How2 change an invoice number automatically and link it?

    OK. Lets start from Accounting 101. You need by law (in most countries) to keep inventory, invoicing and order books updated. Marketing 101 would dictate to be a best practice to keep a tally of what your customers have bought. It is also a good idea to keep track of what and when your suppliers have delivered the goods in order to calculate the turnover (using most of the info you gather in the inventory system). The invoicing module should also feed the accounts receivable and help you to balance your daily transactions.

    Having said that, you need at least a customer file (sheet) with a customer ID, name, contact info, last invoice # and a product file (sheet) with at least product ID, description, stock,cost and a transaction file where all comes together: date,transaction (order,invoice). document #,customer #, product ID, quantity, price

    So when do we start? You would have to design the invoice and order layouts so they can be coded in a form and all the report or summaries layouts too. I am all ears...

  5. #5
    Registered User
    Join Date
    06-20-2014
    Location
    Canada
    MS-Off Ver
    pintree3
    Posts
    9

    Re: How2 change an invoice number automatically and link it?

    You made me laugh rcm. I love you buddy. You're on the ball. :-)
    All I provide is a service (language consultant). I get paid by the hour. There are 3 prices only. [1] Skype/phone [2] written work [3] cultural stuff
    Usually it's just skype/phone.
    So what I have is a customer name with a made up customer ID. Hour worked, price per hour and basically that's it. See attached photo for details whereby my marked red arrows show the 3 important points labelled A,B and C.
    Capture.jpg

  6. #6
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: How2 change an invoice number automatically and link it?

    OK know that I know what you have, and you have recurring customer assignments you want an automated incremental on the invoice correct?
    Last edited by rcm; 06-22-2014 at 02:20 AM. Reason: typo

  7. #7
    Registered User
    Join Date
    06-20-2014
    Location
    Canada
    MS-Off Ver
    pintree3
    Posts
    9

    Re: How2 change an invoice number automatically and link it?

    Sorry rcm for taking so long to reply. I am grateful but have been ill, with surgery and all. And then of course I had to catch up with other things.
    OK so to your question, you asked, "you want an automated incremental on the invoice correct?"
    Sort of, if I understand you correctly. As you can see from ABC above. I want 'A' to change every time I change 'C'. My original question explains it best and by this I mean I don't know how to explain it differently. As of now all I have is 4 clients. So what I do now is I have 5 spreadsheets (5 files with different names) : 1 for each client and a fifth one which just has summary of all the other 4 (their ID number, the receipt no., the dates of work and total charged). So what I do is go to this 5th one see what my last order number is and then open one of the other four and change the order number and fill in the rest, date etc. What I was hoping for was a way to eliminate having to go to my 5th sheet.
    It would be nice basically if when ever I make a change in one the receipt number changes.
    Oh maybe I have another way of explaining it (by metaphor so to speak)
    Assume I have those old school types of receipts. Each booklet has like name and address and stuff and usually in the top corner is a receipt number 001, 002, 003 etc.
    In such a case I would have to fill in all by hand: the customer name, number, amount etc. all except the receipt number which is already stamped.
    So I fill in my 1st receipt, tear it and give it to my customer with a carbon copy for my records.
    OK coll now onto my 2nd customer and receipt. I fill everything in, new customer, new amount etc. all except the receipt number which is already stamped there.
    And on to the next.
    Now here's the difference. Instead of making it of paper, let's make it like a holographic etch-a-sketch. And by this I mean receipt 002 also has the info of receipt 001, and receipt 003 also has the info of 002 and 001 and 004 has 003, 002 and 001 but my etch-a-sketch allows me to erase everything I don't need, change everything else I need and still have my new receipt number which is now 005.
    OK in re-reading this I see how I may have actually confused things. Let me try another way then.
    Assume I have 68 receipts in total of my 4 customers. What I want is for me to be able to take any of these 68 receipts (assume receipt 47) and then as soon as I change the date on this receipt number 47 it goes back to my other 68 receipts, knows that the last one was number 68 and will now change this one (no. 47) to the next one, to number 69--this receipt will of course have to be given a different name so as to not confuse it with the one it was, no.47.
    I mean what does the rest of the world do? Certainly they don't start from scratch on each and every customer and on each and every receipt.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How2 find number in range and return that number or if doesn't exist then the next highest
    By Crawfinator1 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-06-2014, 08:52 AM
  2. Replies: 3
    Last Post: 03-11-2014, 02:30 PM
  3. Replies: 2
    Last Post: 06-10-2012, 11:58 AM
  4. how does an invoice number change automatically ?
    By JudyRuiz in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-08-2005, 12:06 PM
  5. Replies: 1
    Last Post: 02-28-2005, 03:06 PM

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