Making Sequential Invoice Numbers

[dazedandconfused766]

Hi everyone,

In my accounts spreadsheet, I've got a number of different clients each with two digit client codes, and when I invoice the clients, I like the invoice number to be made from the client code and then a sequential number.

So for example client Mr Smith might have a client code SM, while Mr Jones might have JO. If I've done 5 jobs for Mr Smith the next invoice number should be SM06. If I've only done 1 job for Mr Jones, it will be JO02. A brand new client (Mr Chan) would be CH01.

I normally do that manually, but I'm wondering if there's any way that I could do it automatically so that when I enter a new client code, it looks at the last invoice number for that client, and then puts the next invoice number in the invoice number cell for that job.

I guess it's not going to be easy, but I've totally hit a brick wall. I was wondering if anyone had some hints, like some functions which I might be able to use. How can I find the last invoice number for a particular client? Will it require a great deal of coding? Do I need to hire help? I'm trying to do it myself to improve my VBA skills, but I'm going round and round in circles.

Thanks for any comments or suggestions in advance.

David

 

[Dryver14]

how do you seperate mr smith and mr smart

 

[dazedandconfused766]

how do you seperate mr smith and mr smart

That's a good question. I've never actually had that problem, I've only got a few clients, but I guess I would use SR for Smart. Or if it becomes a regular problem I suppose I might have to increase my Client Codes so they have 3 digits. I'll have to keep that in mind moving forward.

 

[shift-del]

Hello David

<table valign="middle" colspan="3" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: bold; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="75,75pt"><col width="96pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="3" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">Client code</td><td align="left">Invoice number</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM01</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO01</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH01</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM02</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH02</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO02</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM03</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH03</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">10</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO03</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">11</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM04</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">12</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH04</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">13</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM05</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">14</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO04</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>B2</td><td>=A2&TEXT(COUNTIF(A$2:A2,A2),"00")</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></tbody></table>

 

[dazedandconfused766]

Hello David

<table valign="middle" colspan="3" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: bold; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="75,75pt"><col width="96pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="3" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">Client code</td><td align="left">Invoice number</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM01</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO01</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH01</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM02</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH02</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO02</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM03</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH03</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">10</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO03</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">11</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM04</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">12</td><td style="font-weight: normal;" align="left">CH</td><td style="font-weight: normal;" align="left">CH04</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">13</td><td style="font-weight: normal;" align="left">SM</td><td style="font-weight: normal;" align="left">SM05</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">14</td><td style="font-weight: normal;" align="left">JO</td><td style="font-weight: normal;" align="left">JO04</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>B2</td><td>=A2&TEXT(COUNTIF(A$2:A2,A2),"00")</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></tbody></table>

Wow! You made it so easy! The COUNTIF function is really useful. I thought it would be really hard, but it's really easy! Thanks thanks thanks thanks thanks!