Next available number in column

[Ana_P]

Hi,

I have an excel s/sheet with order numbers in one column that I would like to prefill based on the next available number in that column.

A few things to note:
- the numbering is a 5 digit number with the first 2 digits being the year the order is in (i.e. 22) followed by 001, 002 etc
- if an order contains multiple items it will use the same order number therefore the next available number will be overridden to accommodate this
- I need the formula to keep working in subsequent rows even if the previous row was overridden per above point
- the data set is in a table
- the numbers aren't in order in the table as the table is sorted by different columns at different times

Any help would be greatly appreciated.

Thanks

 

[Peter_SSs]

I think that a couple of things would help you get some sort of suggestion

1. Update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
   
   Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Ana_P]

I think that a couple of things would help you get some sort of suggestion

1. Update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
   
   Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Peter

I have now updated my account details. Also, I did try to use XL2BB with my original post however it disabled macros on my laptop and when I clicked the add in to follow the steps required, the options of the add-in were greyed out.

Thanks

 

[Peter_SSs]

Without some sample data and an idea of you layout, we would just be guessing.

it disabled macros on my laptop and when I clicked the add in to follow the steps required, the options of the add-in were greyed out.

Might this be related to your issue?
XL2BB-new security won't run xl2bb

 

[Ana_P]

CWOB.xlsx
	A	B	C	D
1	Date Raised	Date Delivered to GD	F/Y	CWO
2	18/10/21	18/01/22	Y	21556
3	18/10/21	18/01/22	Y	21556
4	18/10/21	18/01/22	Y	21556
5	18/10/21	18/01/22	Y	21556
6	18/10/21	18/01/22	Y	21556
7	3/11/21	29/10/21	F	21586
8	3/11/21	29/10/21	F	21586
9	3/11/21	18/01/22	F	21586
10	3/11/21	18/01/22	F	21586
11	3/11/21	18/01/22	F	21586
July-Dec 2022

Cells with Data Validation
Cell	Allow	Criteria
A2:B602	Date	>1/01/1900
C2:C602	List	=Reference!$A$3:$A$7

 

[Ana_P]

Without some sample data and an idea of you layout, we would just be guessing.


Might this be related to your issue?
XL2BB-new security won't run xl2bb

Yes, thanks heaps. Fixed the issue and have now uploaded a part of the worksheet in question. The CWO column is the one my post is about.

 

[Peter_SSs]

Thanks for updating your version details and glad that you got XL2BB working.

Still, I assume that you are trying to produce the numbers in column D but I have no idea how they are arrived at - apart from I can see why the first 2 digits are "21" in each case.
But ..

• How do you come up with 556 for the ending in row 2 given that you said
  

  followed by 001, 002 etc

  That is, why isn't row 2 "21001"?
  
  
• How do you determine that rows 3, 4, 5 & 6 all keep that same ending? You did say "if an order contains multiple items it will use the same order number", but from the sample data, how do we determine where an order starts and finishes?
  
  
• How do you determine that the ending in row 8 changes from 556 to 558?

 

[Ana_P]

Thanks for updating your version details and glad that you got XL2BB working.

Still, I assume that you are trying to produce the numbers in column D but I have no idea how they are arrived at - apart from I can see why the first 2 digits are "21" in each case.
But ..

• How do you come up with 556 for the ending in row 2 given that you said
  
  That is, why isn't row 2 "21001"?
  
  
• How do you determine that rows 3, 4, 5 & 6 all keep that same ending? You did say "if an order contains multiple items it will use the same order number", but from the sample data, how do we determine where an order starts and finishes?
  
  
• How do you determine that the ending in row 8 changes from 556 to 558

Hi Peter

The few rows selected are just a snippet of the data in my data table. So the first order of each calendar year will be Year followed by 001, e.g. first order raised in 2022 will be 22001.

As also mentioned in the initial post, the data is sorted on different columns and therefore the order numbers won't be in order in column D (I.e. CWO column).

As for where an order starts and finishes, that all depends on each customer and each order placed - it's not a given and hence why I said the formula will need to be overridden in that case. Which as I typed that realised may be an issue since its a table format and columns autocalculate! I'm thinking if it's the same Customer (data entered in a column further across the table) with the same "Date Raised" (column A) then it would have the same CWO (column D).

Would you have any suggestions as to how to make it work or are you thinking it's not possible?

Thanks

 

[Peter_SSs]

If you want the numbers to remain the same even when the data is sorted differently then that may not be possible with a formula approach. However, that may depend on just what information that you have in your table. Would need to see some of that - all columns- (with any sensitive data disguised) to help decide.

 

[Ana_P]

If you want the numbers to remain the same even when the data is sorted differently then that may not be possible with a formula approach. However, that may depend on just what information that you have in your table. Would need to see some of that - all columns- (with any sensitive data disguised) to help decide.

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date Raised	Date Delivered	F/Y	CWO	Finisher	Add LT	Customer	Customer PO	Comments	Due Date	Revised ETA	Schedule Date	Schedule Week	Date Received
2	18/10/21	18/01/22	Y	21556	AB		ABC			1/02/22		1/02/22	5	4/02/22
3	18/10/21	18/01/22	Y	21556	AB		ABC			1/02/22		1/02/22	5	4/02/22
4	18/10/21	18/01/22	Y	21556	AB		ABC			1/02/22		1/02/22	5	4/02/22
5	18/10/21	18/01/22	Y	21556	AB		ABC			1/02/22		1/02/22	5	28/02/22
6	18/10/21	18/01/22	Y	21556	AB		ABC			1/02/22		1/02/22	5	31/03/22
7	3/11/21	29/10/21	F	21586	AB		DEF			12/11/21		12/11/21	45	25/02/22
8	3/11/21	29/10/21	F	21586	AB		DEF			12/11/21		12/11/21	45	18/03/22
9	3/11/21	18/01/22	F	21586	AB		DEF			1/02/22		1/02/22	5	15/02/22
10	3/11/21	18/01/22	F	21586	AB		DEF			1/02/22		1/02/22	5	17/02/22
11	3/11/21	18/01/22	F	21586	AB		DEF			1/02/22		1/02/22	5	21/02/22
12	3/11/21	18/01/22	F	21586	AB		DEF			1/02/22		1/02/22	5	25/02/22
13	15/11/21	24/11/21	Y	21611	AB		ABC		In Stock	8/12/21		8/12/21	49	31/01/22
14	16/11/21	17/11/21	F	21613	AB		YZ			1/12/21		1/12/21	48	27/01/22
15	16/11/21	17/11/21	F	21613	AB		YZ			1/12/21		1/12/21	48	28/01/22
16	16/11/21	17/11/21	F	21613	AB		YZ			1/12/21		1/12/21	48	3/02/22
17	17/11/21	30/11/21	F	21625	AB		GHI		ETA 30/11	14/12/21		14/12/21	50	28/01/22
18	22/11/21	22/11/21	Y	21632	AB		GHI			6/12/21		6/12/21	49	18/02/22
19	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	31/01/22
20	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	31/01/22
21	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	31/01/22
22	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	28/02/22
23	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	28/02/22
24	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	31/03/22
25	24/11/21	24/11/21	Y	21642	AB		ABC			8/12/21		8/12/21	49	8/04/22
26	1/12/21	18/01/22	F	21656	AB		HS			1/02/22		1/02/22	5	4/02/22
27	2/12/21	2/12/21	Y	21662	AB		IR			16/12/21		16/12/21	50	28/02/22
28	2/12/21	2/12/21	Y	21662	AB		IR			16/12/21		16/12/21	50	2/03/22
29	2/12/21	2/12/21	Y	21662	AB		IR			16/12/21		16/12/21	50	31/03/22
30	2/12/21	2/12/21	Y	21662	AB		IR			16/12/21		16/12/21	50	31/03/22
31	3/12/21	3/12/21	F	21663	AB		JKL			17/12/21		17/12/21	50	3/02/22
32	6/12/21	6/12/21	F	21669	AB		YZ			20/12/21		20/12/21	51	25/01/22
33	6/12/21	6/12/21	Y	21671	AB		ABC			20/12/21		20/12/21	51	28/02/22
34	6/12/21	6/12/21	Y	21673	AB		GHI			20/12/21		20/12/21	51	4/02/22
35	15/12/21	8/12/21	F	21677	AB		EV			22/12/21		22/12/21	51	1/02/22
36	15/12/21	8/12/21	F	21677	AB		EV			22/12/21		22/12/21	51	11/02/22
37	15/12/21	8/12/21	F	21677	AB		EV			22/12/21		22/12/21	51	11/02/22
38	9/12/21	9/12/21	F	21679	AB		YZ			23/12/21		23/12/21	51	27/01/22
39	9/12/21	9/12/21	F	21679	AB		YZ			23/12/21		23/12/21	51	28/01/22
40	9/12/21	9/12/21	F	21679	AB		YZ			23/12/21		23/12/21	51	15/02/22
41	10/12/21	10/12/21	F	21680	AB		JKL			24/12/21		24/12/21	51	18/01/22
42	14/12/21	14/12/21	F	21686	AB		JKL			28/12/21		28/12/21	52	27/01/22
43	14/12/21	14/12/21	F	21686	AB		JKL			28/12/21		28/12/21	52	27/01/22
44	14/12/21	14/12/21	F	21686	AB		JKL			28/12/21		28/12/21	52	27/01/22
45	14/12/21	14/12/21	F	21686	AB		JKL			28/12/21		28/12/21	52	3/02/22
46	14/12/21	14/12/21	B	21687	CD		FU			8/02/22		8/02/22	6	4/02/22
47	14/12/21	14/12/21	F	21688	CD		GHI			28/12/21		28/12/21	52	24/01/22
48	16/12/21	16/12/21	F	21696	AB		GHI			30/12/21		30/12/21	52	28/01/22
49	16/12/21	16/12/21	F	21699	CD		GHI			30/12/21		30/12/21	52	4/02/22
50	17/12/21	19/01/22	Y	21700	AB		MNO			2/02/22		2/02/22	5	18/02/22
51	17/12/21	19/01/22	Y	21700	AB		MNO			2/02/22		2/02/22	5	28/02/22
52	17/12/21	17/12/21	F	21701	AB		PQR			31/12/21		31/12/21	52	3/02/22
53	17/12/21	17/12/21	F	21702	AB		ABC			31/12/21		31/12/21	52	1/02/22
July-Dec 2022

Cell Formulas
Range	Formula
L2:L53	L2	=IF(ISBLANK([@[Revised ETA]]),[@[Due Date]],[@[Revised ETA]])
M2:M53	M2	=IF(OR([@[Schedule Date]]="",[@[Schedule Date]]="Missing Info"),"",ISOWEEKNUM([@[Schedule Date]]))
J2:J53	J2	=IF(ISBLANK([@[Date Raised]]),"",IF(OR(ISBLANK([@[Date Delivered]]),ISBLANK([@[F/Y]])),"Missing Info",[@[Date Delivered]]+VLOOKUP([@[F/Y]],Lead_Time,3,FALSE)+[@[Add LT]]))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("ASAP",$I2)))	text	NO
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("Urgent",$I2)))	text	NO
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("Repro",$I2)))	text	NO
L2:M601	Expression	=AND($Q2="Open",$M2<Current_Week)	text	NO
J2:J601	Expression	=ISNUMBER($K2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J2:L53	Date	>1/01/1900
N2:N53	Date	>1/01/1900
A2:B601	Date	>1/01/1900
C2:C601	List	=Reference!$A$3:$A$7
E2:E601	List	=Reference!$A$15:$A$17
F2:F601	Whole number	between 0 and 99