Create an Automated document numbering

[Pestomania]

I am trying to identify how to set N2 to identify the "next" available number for a document type. If you were to put Facility 10, Dept 1 in row 5, it would produce DOC-7001 as the answer. No numbers can be reused, only unique identifiers.

Please see below details, hopefully you can help me figure something out!

Using Microsoft 335 Apps for Enterprise, Version 2308 (Build 16731.20636)

BCP Document Numbering Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Site Name	Type of Document	Start	End	# of Procedures	Concatenate				Site Names	# Start Range			Document Number	Facility Name	Department	Sub-Title
2	Facility 10	Dept 1	7000	7010	10	7000-7010				Facility 1	7900			DOC-7000	Facility 10	Dept 1	Sub-title 1
3	Facility 10	Dept 2	7011	7030	19	7011-7030				Facility 2				DOC-7101	Facility 10	Dept 12	Sub-title 2
4	Facility 10	Dept 3	7031	7040	9	7031-7040				Facility 3	7200			DOC-7255	Facility 3	Dept 5	Sub-title 3
5	Facility 10	Dept 4	7041	7055	14	7041-7055				Facility 4	7300
6	Facility 10	Dept 5	7056	7060	4	7056-7060				Facility 5	7400
7	Facility 10	Dept 6	7061	7065	4	7061-7065				Facility 6	7500
8	Facility 10	Dept 7	7066	7070	4	7066-7070				Facility 7	7600
9	Facility 10	Dept 8	7071	7075	4	7071-7075				Facility 8	7700			I want to set up Document Number to be automated to use the "Facility Name" and "Dept" to identify the next document number available based on the ranges in the blue table.
10	Facility 10	Dept 9	7076	7080	4	7076-7080				Facility 9	7800
11	Facility 10	Dept 10	7081	7085	4	7081-7085				Facility 10	7000
12	Facility 10	Dept 11	7086	7100	14	7086-7100				Facility 11
13	Facility 10	Dept 12	7101	7105	4	7101-7105
14	Facility 10	Dept 13	7106	7110	4	7106-7110
15	Facility 10	Dept 14	7111	7115	4	7111-7115
16	Facility 10	Dept 15	7116	7120	4	7116-7120
17	Facility 10	Dept 16	7121	7130	9	7121-7130
18	Facility 10	Dept 17	7131	7145	14	7131-7145
19	Facility 10	Dept 18	7146	7150	4	7146-7150
20	Facility 10	Dept 19	7151	7155	4	7151-7155
21	Facility 3	Dept 1	7200	7210	10	7200-7210
22	Facility 3	Dept 2	7211	7216	5	7211-7216
23	Facility 3	Dept 3	7217	7226	9	7217-7226
24	Facility 3	Dept 4	7227	7247	20	7227-7247
25	Facility 3	Dept 5	7248	7264	16	7248-7264
26	Facility 3	Dept 6	7265	7286	21	7265-7286
27	Facility 3	Dept 7	7287	7297	10	7287-7297
Data Tables

Cell Formulas
Range	Formula
C3:C20,C22:C27	C3	=D2+1
D2:D27	D2	=[@Start]+[@['# of Procedures]]
F2:F27	F2	=C2&"-"&D2

Cells with Data Validation
Cell	Allow	Criteria
A2:A27	List	=Site_Names
O2:O4	List	=Site_Names

 

[hagia_sofia]

I am not sure whether the following is what you want but you can give it a try in N5 (it should fill the value once you enter facility name and department):

=LET(a,MAX(--(RIGHT(FILTER(N$2:N4,(O$2:O4=O5)*(P$2:P4=P5)),4))),b,XLOOKUP(O5&P5,$A$2:$A$27&$B$2:$B$27,$D$2:$D$27),c,XLOOKUP(O5&P5,$A$2:$A$27&$B$2:$B$27,$C$2:$C$27),IFERROR(IFS(a<b,"DOC-"&a+1,a=b,0),"DOC-"&c))

 

[Pestomania]

I am not sure whether the following is what you want but you can give it a try in N5 (it should fill the value once you enter facility name and department):

=LET(a,MAX(--(RIGHT(FILTER(N$2:N4,(O$2:O4=O5)*(P$2:P4=P5)),4))),b,XLOOKUP(O5&P5,$A$2:$A$27&$B$2:$B$27,$D$2:$D$27),c,XLOOKUP(O5&P5,$A$2:$A$27&$B$2:$B$27,$C$2:$C$27),IFERROR(IFS(a<b,"DOC-"&a+1,a=b,0),"DOC-"&c))

This worked phenomenally for everything except in N2. Since i am hoping to make this a table, if I was able to overwrite cells in that column, that would be awesome!

I am unsure if there is a way to "lock" in a document number or if that would be better suited in Microsoft Access. Now that i am thinking about it, that may be my better option for maintaining a table.

 

[hagia_sofia]

Ah, of course I forgot about the 1st occurence. Try to place the following madness into N2:

="DOC-"&LET(b,XLOOKUP(O2&P2,$A$2:$A$27&$B$2:$B$27,$C$2:$C$27),c,XLOOKUP(O2&P2,$A$2:$A$27&$B$2:$B$27,$D$2:$D$27),IFERROR(IF(ROW()=2,b,IF(AND(ROW()<>2,MAX(--(RIGHT(FILTER(N$1:N1,(O$1:O1=O2)*(P$1:P1=P2)),4)))<c),MAX(--(RIGHT(FILTER(N$1:N1,(O$1:O1=O2)*(P$1:P1=P2)),4)))+1,"NN")),XLOOKUP(O2&P2,$A$2:$A$27&$B$2:$B$27,$C$2:$C$27)))

 

[Pestomania]

Ah, of course I forgot about the 1st occurence. Try to place the following madness into N2:

="DOC-"&LET(b,XLOOKUP(O2&P2,$A$2:$A$27&$B$2:$B$27,$C$2:$C$27),c,XLOOKUP(O2&P2,$A$2:$A$27&$B$2:$B$27,$D$2:$D$27),IFERROR(IF(ROW()=2,b,IF(AND(ROW()<>2,MAX(--(RIGHT(FILTER(N$1:N1,(O$1:O1=O2)*(P$1:P1=P2)),4)))<c),MAX(--(RIGHT(FILTER(N$1:N1,(O$1:O1=O2)*(P$1:P1=P2)),4)))+1,"NN")),XLOOKUP(O2&P2,$A$2:$A$27&$B$2:$B$27,$C$2:$C$27)))

Awesome!!! That works!

 

[hagia_sofia]

That is good to hear. Just note that at the moment the formula is constructed that it relies on the first calculation being in the 2nd row (cf. the ROW()=2/ROW()<>2 part), and it returns DOC-NN when there are more procedures than allowed (I am not sure whether this could happen).