Serial number creation / pre-defined alphanumeric sequence / formula

Hi all,

I'm new here but i'm a heavy user of Excel. I've not got much into programming but heavily use pivots and lots of formula.... which leads me to my conundrum.

I am creating a list of serial numbers that are in the following format:-

A1 B1 C1 D1 E1 F1 G1
MODEL/COUNTRY - MONTH/YEAR - NUMERIC SEQUENCE


for Example

15UK-1012-0101

The 15 denotes our 150 model (at the moment there are only 7 models), the UK is United Kingdom (we only distribute to about 15 countries), the 1012 is month and year and then the number sequence will start at 0101 and end at 9999.

I am using a concatenation formula to put this all together so the date used can change just by opening up the sheet (I'm using the following for date =TEXT(TODAY(),"MMYY") however the though then crossed my mind about how to make excel remember which serial numbers have been used.

If i allocate say 40 serial numbers, i want Excel to then use the next available numbers in the sequence (so looking at column G) and strikeout the numbers which have been allocated - i guess there'll need to be some form of macro that will 'save' and therefor strickout numbers which have been used.

Our manufacturing plant will create about 100 units a month and I want to give one person the power to simply select the unit, the country and then the number of units that have been manufactured and then excel will display the correct number of serials in the correct format and when he SAVE / PRINTS / CLOSES the sheet it will remember and so the next time he opens, the date will adjust its self and the number sequence will simply start from where it left off.

I have though that this will need to be restricted to one user (for the sake of continuity) but can excel do this?

Can it be used progressively like this?

Any help would be greatly appreciated!

Hi, Check if the attached file helps.

Regards,
Sindhu