I have 4 items in a drop-down menu:

CEO
CORPCOMMS
AUDIT
SE

I need a unique number to be generated depending on which item is selected and depending on the year of the entry. For instance:
  • If in cell A1, I select CEO, I need a unique number to be generated in A2: 14-CEO-01; "14" is the year 2014;
  • If in cell B1, I select CEO again, B2 should have the number: 14-CEO-02;
  • If CORPCOMMS is selected in cell C1, C2 should automatically have the number: 14-CORPCOMMS-01;
  • CEO is selected again in cell D1, D2 will generate: 14-CEO-03;

Let's assume there are 300 entries for CEO in 2014. The 301st CEO entry is in 2015, and the number to be generated for that would be: 15-CEO-01.

Is this possible?

Alternatively, I can ask the users to enter their unique ID manually, and then display the last entry for each selection in a table. For instance:

CEO: 14-CEO-51
CORPCOMMS: 14-CORPCOMMS-20
AUDIT: 14-AUDIT-5
SE: 14-SE-11

I'm not sure how to do that either.

Any help will be much appreciated.