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:

	A	B	C	D
1	DIV	DOC	SEQ	DOCUMENT NUMBER
2	GRO	SOW	00001	GOW-SOW-00001
3	GRO	SOW	00002	GOW-SOW-00002

<tbody>
</tbody>

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

Thanks for your help

 

[oldbrewer]

DIV	DOC	SEQ	DOCUMENT NUMBER
GRO	SOW	1	GOW-SOW-00001
GRO	SOW	2	GOW-SOW-00002
GO	SLOW	1
GO	SLOW	2
GRO	SOW	3
GRO	SOW	4
GO	SLOW	3
GO	SLOW	4
SO	SLOW	1
		FORMULA GIVING THE 1 UNDERNEATH SEQ
		=SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))
		and just drag down
		format as 00000 obviously

<colgroup><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>

 

[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")