Cross Referencing two workbooks to create unique invoice number

[gdadash]

Hi, I'm sure this has been answered somewhere before...
I have an invoice template with 2 sheets, the first sheet is the template and the second sheet is customer names and addresses. I use 1 cell to input a code for the customer, which then fills in their name/address details and their agreed rate of pay. Then I just have to fill in the details for amount of hours etc
The 2nd workbook is split into monthly lists of invoices with due date and a hyperlink back to the original file saved as a pdf
What I am looking to do is when I input the the customer code for the address details etc the invoice number for that customer is automatically added, the invoices are numbered by the customer code followed by a number eg:- cw001

Thanks

 

[ajm]

the answer could be as simple as creating a list of used invoice numbers for each customer and then when you need the next one, your macro refers to the list and then adds 1 to the last invoice number used. but first, a question: does your "invoice template" create the invoice or does it merely record details from the invoice?

 

[gdadash]

The invoice template adds the correct day/hour rate for the customer concerned and then I fill in the number of hours and any extra info they might need to identify the work etc. After that I save it as a PDF to email to the client.

 

[ajm]

ok. so two workbooks. two tabs in Workbook 1. Tab 1 is the Invoice Template and Tab 2 has your customer list. You mention inputting a code to get the customer details; do you select this from a drop down box (as in Data Validation), or do you type it in manually? Are there vlookup formulas in the cells where the customer details are needed?

Workbook 2 has 12 tabs (Jan - Dec). Or is it one tab with row after row of data?

perhaps you could post a sample of the invoice template, and the monthly lists of invoices?

working on the assumption that your invoice lists in Workbook 2 have a range on each of the months tabs for each of your customers, I would use named ranges to hold the used invoice numbers, where the range of used invoice numbers reflects the customer code you enter in workbook 1.

for example, creating an invoice for customer IBM. i open the template workbook, type "IBM" into the customer code cell, or select it from a drop down box. I also select or input the date/month relating to the work to be invoiced. Customer details are obtained from the second tab. at the same time, using a worksheet change event, a macro opens the file Used Invoice Numbers.xlsm (or whatever your 2nd file is called). in this workbook, the tab names are the months. so, for our IBM invoice, its May, so we go to to the May tab. On this tab are several sets of used invoice numbers relating to each of our customers. Each set is named according to the customer. In the IBM named range, our macro would find the last value used and add +1 in the next cell down. this new number is now your new invoice number which gets written back to the Invoice template sheet.

could this scenario work for you? like you say in the start of your post, there are probably other ways of doing this same thing (google autonumbering), but this would be the way I would do it. have you much experience with MS Visual Basic?

 

[gdadash]

That sounds like the sort of thing im looking at, but sadly i have very little experience of visual basic, is it quite easy to pick up?

 

[ajm]

my apologies, i wrote a response to you last week but because it kept timing out, i put it into a workbook so that i could then copy it across to MrExcel but obviously forgot. So here it is:

lets try it in a simpler fashion using formulas then. I am confident it could still work this way. if we start with the existing invoices workbook. name each tab Jan to December. On each tab, you will then need to make a named range for each of your customers which will hold your used invoice numbers. For the range names for each customer, use their customer code (that you input into the template to get their address details). Initially you used cw001 as an example; in this case cw will be your customer code in the template and your range name in the used invoices workbook. Assuming that you have already invoiced your customers for Jan to present, we can easily set them up first. On the first tab, called January, do the following.


Have a look at my table below:

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com)  -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
 <tr>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Next Invoice Number</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>015</b></font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>006</b></font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>009</b></font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>008</b></font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">New Invoice Number</font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">IBM015</font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">WOW006</font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">FNB009</font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">ABC008</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Customer Code</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">IBM</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">WOW</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">FNB</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">ABC</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">001</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">001</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">001</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">001</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">002</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">002</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">002</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">002</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">003</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">003</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">003</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">003</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">004</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">004</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">004</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">004</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">005</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">005</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">005</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">005</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">006</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">006</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">006</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">007</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">007</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">007</font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">008</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">008</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">009</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">010</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">011</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">012</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">013</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">014</font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
 <tr>
 <td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 <td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td>
 </tr>
</table>

In A2, input "Next Invoice Number".

In A4, input "New Invoice Number"

In A6, input "Customer Code"

in B6, input your first Customer Code, eg IBM

in B7 down, list all the existing invoice numbers (without the alpha characters)
**Change the format for the cells that you list the invoice numbers into a custom format. Select the range, say B7 to B100, right click, select Format Cells, select CUSTOM, type 000 (three zeroes) into the Type box, then click OK. Don't worry if you go back to check the format and it shows up as something else. So long as it looks like it should, we are all good

In B2, input "=MAX(B7:B100)+1" - make sure the range here is long enough to cover the number of invoices that this particular customer will receive in one month.

In B4, input "=B6& TEXT(B2,"000")"

Start by doing just the first customer first. you can then copy the column across the page for as many customers that you have. Also, just do January first. When you have set it up for all customers, you can then copy the tab 11 more times, each time renaming to the next month, and adding the already used invoice numbers.

finally, for the moment atleast, make the "New Invoice Number" for each customer a named range. remembering that each customer will appear on each tab, we will need to be able to differentiate January IBM invoice numbers from March IBM invoice numbers. in the example i have set up above, B4 gets the name "IBMJAN", D4 "WOWJAN" and so on.

I just re-read your post: you will probably want to insert a couple of rows in between each customer so you can record due date and the hyperlink etc.

Come back to me when you have this workbook set up as described above. I have to get some work done now.