Creating Unique Batch or Lot Numbers

[jacob@thepenpoint]

Greetings all

I am working on a workbook to generate batch numbers for production. The Numbers are in the form YYMxxx. A 2 digit year, a 1 letter Month (sequential alpha chars basically) and a 3 digit sequence number. File attached.

What I have is very cumbersome in that for every number generated 4 cells are written, batch#, Month, Year, & Seq. If you have to go back and enter a number after the month changes the sequence starts over duplicating a batch number which is not acceptable, and the same is true if the list gets sorted, the batch numbers could end up changing.

I would also like to simplify the entire process so that maintaining the spreadsheet would be easier. I started this several months ago, and for the life of me, the calculation in column F has a text value being multiplied by 29 and I have no idea how that calculation came to be or what it actually does other than it doesn't work without it.

There is not a problem with using VBA if that will provide a more workable solution as I am planning to create a routine to add a row at the top rather than have a finite number of rows set up with formulas that has to be added to or put into a formal table. For that matter, I don't have a problem with using a table either.

I would certainly appreciate anyone who could help with this or point me into the right direction. Right now, I am not seeing anything but problems.

Thanks!