Automatic Numbering in Excel

Hi there

I’m having a problem with an automatically numbered list in Excel. I am trying to consolidate two separate invoicing workbooks into one – the two workbooks refer to different countries.

What I would like to achieve is that when a user picks a customer from a dropdown list (coming from another sheet in the same workbook) and the customer’s country is determined using VLOOKUP (lets say it will be either IRL or UK), that excel will automatically generate an invoice number in column A depending on the following:

If the customer is from IRL the invoice number will be the lowest unused number between 100000 and 199999.
If the customer is from the UK the invoice will be the lowest unused number after 200000.

I have tried a number of different things including:

Complex If statements incorporating Max and Min Formulas (this creates circular references as the formula is in column A and it gets the max of column A)
An IRL and a UK helper column which increment if (country=”IRL”) or (country = “UK”) respectively and then add this number on to either 100000 or 200000 (depending on IRL or UK client) – this works OK, but if a sort is performed (which it will be) the invoice numbers no longer correspond to the order they originally corresponded to.


I have a feeling that this isn’t suitable for Excel but would like to get your opinions if possible – even if just to say I should give up!

Thanks a million
Liam