The challenge is on! I have searched for other formulas for creating unique invoice auto-numbering but not satisfied with anything found thus far - I do not want to use VBA as the person managing the sheet has minimal excel skills and experience so want to keep it simple. Hopefully I have explained the following scenario well enough that it is understandable.
I tried creating a unique number to use as an invoice number using the "formula helper column". This may or may not be the right method so please anyone suggest a better alternative. I am using the assumption that a customer can make multiple purchases within a given month on different days but the formula needs to assign a unique same number for the multiple transactions within that month. Then on a separate sheet is the invoice template where the formula uses the concatenation of the Customer name + billing month to return the one invoice number for the multiple items regardless of what date each item was transacted on.
I started with this formula in the Invoice # column =IF(A2="","",E2&"-"&TEXT(COUNTIF(E$2:E13,E13),"0000")) but as you can see from the sample below every instance of Feb-ABC has a different invoice number when I want them to have the same number eg: in this sample the first 4 entries should have Feb-ABC-0001, the 5th entry should have Feb-DEF-0002, and the 6th entry Mar-ABC-0003 - or something like that to make it unique numbering. The transactional items may not be in a sequential order like those in the sample either ie: they could be anywhere within a range for a month.
Look forward to a solution.
<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>
</tbody>
I tried creating a unique number to use as an invoice number using the "formula helper column". This may or may not be the right method so please anyone suggest a better alternative. I am using the assumption that a customer can make multiple purchases within a given month on different days but the formula needs to assign a unique same number for the multiple transactions within that month. Then on a separate sheet is the invoice template where the formula uses the concatenation of the Customer name + billing month to return the one invoice number for the multiple items regardless of what date each item was transacted on.
I started with this formula in the Invoice # column =IF(A2="","",E2&"-"&TEXT(COUNTIF(E$2:E13,E13),"0000")) but as you can see from the sample below every instance of Feb-ABC has a different invoice number when I want them to have the same number eg: in this sample the first 4 entries should have Feb-ABC-0001, the 5th entry should have Feb-DEF-0002, and the 6th entry Mar-ABC-0003 - or something like that to make it unique numbering. The transactional items may not be in a sequential order like those in the sample either ie: they could be anywhere within a range for a month.
Look forward to a solution.
| Transaction Date dd/mm/yyyy | Invoice Billing Date | Financial Year | Billing Period | Formula Helper Column (Hide) | Invoice # | Revenue Type | Customer Name |
| 25/02/2015 | 28/02/2015 | 2015 | 2 | Feb-ABC | Feb-ABC-0006 | Product Sales | ABC |
| 5/02/2015 | 28/02/2015 | 2015 | 2 | Feb-ABC | Feb-ABC-0007 | Product Sales | ABC |
| 10/02/2015 | 28/02/2015 | 2015 | 2 | Feb-ABC | Feb-ABC-0008 | Product Sales | ABC |
| 10/02/2015 | 28/02/2015 | 2015 | 2 | Feb-ABC | Feb-ABC-0009 | Product Sales | ABC |
| 21/02/2015 | 28/02/2015 | 2015 | 2 | Feb-DEF | Feb-DEF-0010 | Massage Therapy | DEF |
| 20/03/2015 | 31/03/2015 | 2015 | 3 | Mar-DEF | Mar-DEF-0011 | Product Sales | DEF |
<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>
</tbody>
