Forum Discussion
Auto-numbering for invoices
I used Xero for my invoicing and when i upload my spreadsheet to Xero i need to manually input the invoice numbers.
Is there a way that i can use a formula to automat this process and save me time so i dont need to input the invoice number each time?
This issue i have that i have the same company on more than 1 line and it need to have the same invoice number. Below is what i mean.
| Company | Invoice number |
ABC ltd | INV-0001 |
| ABC ltd | INV-0001 |
| ABC ltd | INV-0001 |
| 123 ltd | INV-0002 |
| 123 ltd | INV-0002 |
| 123 ltd | INV-0002 |
| XYZ ltd | INV-0003 |
Many thanks
try
="INV-"&TEXT(XMATCH( [@Company], UNIQUE( [Company] ) ), "0000")
if it isn't formatted as a Table then replace [@Company] with A2 and [Company] with A2:.A9999
3 Replies
try
="INV-"&TEXT(XMATCH( [@Company], UNIQUE( [Company] ) ), "0000")
if it isn't formatted as a Table then replace [@Company] with A2 and [Company] with A2:.A9999
That works great thank you.
The only thing i need to know now is how do i continue from my last invoice number. IE from INV-026302
if it is a 'clean' start then:
="INV-"&TEXT(XMATCH( [@Company], UNIQUE( [Company] ) )+26302, "000000")
but if you still have the other companies in the list then you need to account for that and it will depend on some scenarios:
a) lines A1-A100000 are the 'old' data with companies that are no longer used and the last invoice is INV-026302. so going forware starting on line 100001 should be only new companies names and new invoice numbers starting after 026302. then:
="INV-"&TEXT(XMATCH( A100001:.A1048576 , UNIQUE( A100001:.A1048576 ) )+26302, "000000")
b) lines A1-A100000 are the 'old' data with companies (and invoices are already entered in column B) but those same companies may show up again and should use the prior Invoice number then you could use:
=XLOOKUP( A100001, A$1:A100000, B$1:B100000, "INV-"& 1+MAX(--TEXTAFTER(B$1:B100000, "-")))
And then copy and paste down as needed
