Next available reference number

[zefrogi]

Hi, looking for a little help on how to improve my next available reference formula to include gaps.

The next available ref: AA007
Ref	Heading
AA001	Test1
AA003	Test2
AA004	Test3
AA006	Test4

At the moment I have code:

="The next available ref: AA"&SUM(AGGREGATE(14,6,NOT(ISBLANK(Table[Ref]))*RIGHT(Table[Ref],3)*1,1),1)

Which would read: AA007 - is there any way I can make it read AA002 without using VBA or a helper column?

 

[jasonb75]

This should do it, subject to AA being the only prefix used. If you want the prefix to change when the reference number reaches 999 then that will require a different approach and may only be possible with vba.

Book1
	A
1	The next available ref: AA002
2	Ref
3	AA001
4	AA003
5	AA004
6	AA006
Sheet2

Cell Formulas
Range	Formula
A1	A1	="The next available ref: AA"&TEXT(MATCH(TRUE,COUNTIF(Table[Ref],"AA"&TEXT(ROW(Table)-ROW(Table[#Headers]),"000"))=0,0),"000")

 

[zefrogi]

This should do it, subject to AA being the only prefix used. If you want the prefix to change when the reference number reaches 999 then that will require a different approach and may only be possible with vba.

Book1
	A
1	The next available ref: AA002
2	Ref
3	AA001
4	AA003
5	AA004
6	AA006
Sheet2

Cell Formulas
Range	Formula
A1	A1	="The next available ref: AA"&TEXT(MATCH(TRUE,COUNTIF(Table[Ref],"AA"&TEXT(ROW(Table)-ROW(Table[#Headers]),"000"))=0,0),"000")

Champion! Thank you.