How to: Generate next sequential number after two dropdown cell options?

[Thall_WA]

Hi,

I am creating a master register for internal business documents, each shall have their own unique document number.
I have a dropdown for cells under DIV and DOC, with list parameters on separate datasheet. I have a partial formula within DOCUMENT NUMBER column of =($A2&"-"&$B2&"-"&$C2), which combines DIV selection and DOC selection and the SEQ sequential number (separate with "-"). But I need the sequential number generated, based off how many times the combination of DIV+DOC has been selected previously. I'm hoping this will be enough, but will have a failsafe of conditional duplicate formatting on just in case.

Looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DIV[/TD]
[TD]DOC[/TD]
[TD]SEQ[/TD]
[TD]DOCUMENT NUMBER[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GRO[/TD]
[TD]SOW[/TD]
[TD]00001[/TD]
[TD]GOW-SOW-00001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GRO[/TD]
[TD]SOW[/TD]
[TD]00002[/TD]
[TD]GOW-SOW-00002[/TD]
[/TR]
</tbody>[/TABLE]


Am I on the right track, or is there alternate/easier solution?

Thanks for your help

 

[oldbrewer]

[TABLE="width: 496"]
<colgroup><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD]DIV[/TD]
[TD]DOC[/TD]
[TD]SEQ[/TD]
[TD]DOCUMENT NUMBER[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GRO[/TD]
[TD]SOW[/TD]
[TD="align: right"]1[/TD]
[TD]GOW-SOW-00001[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GRO[/TD]
[TD]SOW[/TD]
[TD="align: right"]2[/TD]
[TD]GOW-SOW-00002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GO[/TD]
[TD]SLOW[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GO[/TD]
[TD]SLOW[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GRO[/TD]
[TD]SOW[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GRO[/TD]
[TD]SOW[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GO[/TD]
[TD]SLOW[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GO[/TD]
[TD]SLOW[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SO[/TD]
[TD]SLOW[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]FORMULA GIVING THE 1 UNDERNEATH SEQ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]and just drag down[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]format as 00000 obviously[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Ruca13]

To generate the sequential number, I would create an aux column with DIV & DOC, and then on column D use =countif($C$1:$C1,C1).

This way, the first combination will generate a 1, the second time it appears a 2 and so on.

Then DIV & DOC & Text(D1,"00000").

Hope it helps

 

[Tetra201]

Try this:

=$A2&"-"&$B2&"-"&TEXT(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2),"00000")