How to set up a formula for an Alphanumeric numbering system

[RitaK]

I am not so great on formulas and am trying to set up a numbering system for
documents

Each reference comprises of a 3 letter reference (abbreviated form of
document types) plus a number from 1000 onwards

Eg

ABC1000 onwards
IDAS1000 onwards
QCS1000 onwards

Row A would be the header row and there are 10 abbreviated departments
Each time I wanted to add a doc to the department I would need to choose
next sequential number for that dep

Is there anyone who has any ideas re this?

thanks

 

[Luke M]

Let's say you have ABC1000 in cell A2. Sequential formula is:
=LEFT(A2,3)&VALUE(MID(A2,4,255))+1

Is this what you were looking for?

 

[Gary''s Student]

Very easy if you use two columns. Say in A1 thru B10 we have something like:

abc 999
def 999
ghi 999
jkl 999
mno 999
opr 999
stu 999
vwx 999
yza 999
bcd 999

Column A has the codes.
Column B has one less than the starting value.

In B11 enter the following array formula:

=IF(A11="","",MAX(IF($A$1:A10=A11,$B$1:B10))+1)

It MUST be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. If
you enter it improperly you will see #VALUE!

Copy B11 on down the column. Now if you enter codes in column A from A11 on
down, column B will automatically pick the next highest namber for that code.
For example:

abc 999
def 999
ghi 999
jkl 999
mno 999
opr 999
stu 999
vwx 999
yza 999
bcd 999
opr 1000
opr 1001
opr 1002
stu 1000
stu 1001