Return next sequential number with prefix - prefix varies

[Dani_RF]

Hi All,

I have a worksheet which has a job numbering sequence whereby I need to know the next job number that's available.
There are 3 different pre-fixes required, and the job numbers are sequential for each prefix.

Prefix refers to a different brand in the business.

For example;
Prefixes:
MDML
MJF
ULP

Number sequence:
000-001, 000-002, 000-003 etc

So one brand's first job number is MDML-000-001 and is sequential from there.


Issue is, we are tracking all job numbers on the one sheet as each brand is under the same company name.
We need to allocate the next sequential number for that brand automatically.


I wanted to have a drop down in say cell A:3 to select the brand, then populate B:3 with the next sequential number for that brand.
I have tried dependant dropdowns, but its too manual and chances for duplication is too high.

Is there a way to do this without manually having to check what number we are up to?

 

[kvsrinivasamurthy]

In B3 then Copy down

=A3&"-000-"& TEXT(COUNTIF($A$2:$A3,$A3),"000")

 

[kvsrinivasamurthy]

If last three exceeds 999 then instead of previous formula you can use
In B3 then copy down.

=A3&"-"& TEXT(COUNTIF($A$2:$A3,$A3),"000-000")

 

[Dani_RF]

Champion - thankyou!!!