Unique incremental numbering formula

[Jon3sy]

I have a spreadsheet that has a tab with sections and within each section there will be tasks added – each task item requires a unique number and must increment to the next available number within a specified number range based on a Work type.

The first 2 parts of the number are based on two fields concatenated from somewhere else on another tab in the spreadsheet. The number format is XXX.X-XXXX.XXX – it is this last sequence of numbers that must increment.

So hopefully I can explain this so it makes sense - if work type in cell $I$9 = “Operations” – the incremental part of the number must begin within the range of 001-199, if = “Steaming” – the incremental number must begin within the range of 200-399, if = “General” – the incremental number must begin within the range of 400-599, if = “Projects” – the incremental number must begin within the range of 600-699.

So formula needs to start something like if($E$1 = “”,””,$I$1&”.”&$E$1&”.” then I get lost from here…

Section 1
Tag #
XXX.X-XXXX.001
XXX.X-XXXX.002
XXX.X-XXXX.003

Section 2
Tag #
XXX.X-XXXX.200
XXX.X-XXXX.201
XXX.X-XXXX.202

Section 3
Tag #
XXX.X-XXXX.400
XXX.X-XXXX.401
XXX.X-XXXX.402

And so on - if we can start with that and see what any of you masters can come up with will be much appreciated.

Thanks in advance

 

[James006]

Hello,

Just for consistency purposes ...

Does Section 4 requires 600 ...???

 

[James006]

Hello again,

With the first header Section 1 located in cell A2 ... in order to get the correct number of Hundreds ... you can test in cell B2, the following array formula

=IF(A2="","",(VALUE(SUBSTITUTE(INDEX(INDIRECT("A"&MAX(IF($A$1:A2="",ROW($A$1:A2)))):A2,MATCH("Section"&"*",INDIRECT("A"&MAX(IF($A$1:A2="",ROW($A$1:A2)))):A2,0)),"Section ",""))-1)*2)

Hope this will get you going ...