Looking for a formula to display the week an item is running out of stock

[MrTinkertrain]

Dear Excel-gurus,

I have got the following (mini)sheet

Concept VMI analyse-voorbeeld VMI.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
5	ArtikelCode	EenheidCode	Stock customer	Stock in transit	Stock available	Still to produce	WK02	WK03	WK04	WK05	WK06	WK07	WK08	WK09	WK10	WK11	WK12	WK13	WK14	WK15	WK16	WK17	WK18	WK19	WK20	WK21
6	L560.65.0125	KG	317					39		39								39	118
7	L560.65.0120	KG	259		555			171	132								118		277				118
8	L560.65.0122	KG	582				158		79								79		79
9	L560.65.0124	KG	861							229		229
10	L560.65.0123	KG	2172		545		877		832			178				661			1024				1612
11	L560.65.0126	KG	1271	1500			398	178	178			183					362		546				435
12	L560.65.0119	KG	2045	5000	3922		1596	1258	178			481	178	178	1686		535		4630				893
13	L560.65.0121	KG	1679													661			441				863
14	L560.65.0118	KG	799				143		135	32		112	32	48		32	48	60	309				393
15	L560.65.0117	KG	970	2000	473		117	161	173	205	154	332	225	241	173	668	48	60	852				1153
16	L560.65.0116	KG	503	1000				43		299		255						43	131
17	L560.65.0115	KG	1479	1000			603	341	198			204	38	38		200	402	38	740				643
18	L560.65.0114	KG	1667	1500			1135		1052			198		96	96	992	95		1137				1949
19	L560.65.0113	KG	2842	5000	3302		2054	1587	345			534	237	259	1933	221	787		5448				1284
20	L560.65.0112	KG	1964	815	815		175		88							734	87		577				959
21	L560.65.0109	KG	687
22	L560.65.0106	KG	1115		1371		613					208		226		613							839
23	L560.65.0082	KG	2269	2000			680					231		251		680							932
24	L560.65.0055	KG	707	112	112			46											27
25	L560.65.0067	KG	404	102	102					62	22			62				62	62				61
8589000016317_200106

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6:J25	Expression	=SOM($F6:$I6)<SOM($J6:$J6)	text	YES
J6:J25	Expression	=SOM($F6:$I6)>=SOM($J6:$J6)	text	YES
K6:K25,K28:K47	Expression	=SOM($F6:$I6)<SOM($J6:$K6)	text	YES
K6:K25,K28:K47	Expression	=SOM($F6:$I6)>=SOM($J6:$K6)	text	YES
L6:L25,L28:L47	Expression	=SOM($F6:$I6)<SOM($J6:$L6)	text	YES
L6:L25,L28:L47	Expression	=SOM($F6:$I6)>=SOM($J6:$L6)	text	YES
M6:M25,M28:M47	Expression	=SOM($F6:$I6)<SOM($J6:$M6)	text	YES
M6:M25,M28:M47	Expression	=SOM($F6:$I6)>=SOM($J6:$M6)	text	YES
N6:N25,N28:N47	Expression	=SOM($F6:$I6)<SOM($J6:$N6)	text	YES
N6:N25,N28:N47	Expression	=SOM($F6:$I6)>=SOM($J6:$N6)	text	YES
O6:O25,O28:O47	Expression	=SOM($F6:$I6)<SOM($J6:$O6)	text	YES
O6:O25,O28:O47	Expression	=SOM($F6:$I6)>=SOM($J6:$O6)	text	YES
P6:P25,P28:P47	Expression	=SOM($F6:$I6)<SOM($J6:$P6)	text	YES
P6:P25,P28:P47	Expression	=SOM($F6:$I6)>=SOM($J6:$P6)	text	YES
Q6:Q25,Q28:Q47	Expression	=SOM($F6:$I6)<SOM($J6:$Q6)	text	YES
Q6:Q25,Q28:Q47	Expression	=SOM($F6:$I6)>=SOM($J6:$Q6)	text	YES
R6:R25,R28:R47	Expression	=SOM($F6:$I6)<SOM($J6:$R6)	text	YES
R6:R25,R28:R47	Expression	=SOM($F6:$I6)>=SOM($J6:$R6)	text	YES
S6:S25,S28:S47	Expression	=SOM($F6:$I6)<SOM($J6:$S6)	text	YES
S6:S25,S28:S47	Expression	=SOM($F6:$I6)>=SOM($J6:$S6)	text	YES
T6:T25,T28:T47	Expression	=SOM($F6:$I6)<SOM($J6:$T6)	text	YES
T6:T25,T28:T47	Expression	=SOM($F6:$I6)>=SOM($J6:$T6)	text	YES
U6:U25,U28:U47	Expression	=SOM($F6:$I6)<SOM($J6:$U6)	text	YES
U6:U25,U28:U47	Expression	=SOM($F6:$I6)>=SOM($J6:$U6)	text	YES
V6:V25,V28:V47	Expression	=SOM($F6:$I6)<SOM($J6:$V6)	text	YES
V6:V25,V28:V47	Expression	=SOM($F6:$I6)>=SOM($J6:$V6)	text	YES
W6:W25,W28:W47	Expression	=SOM($F6:$I6)<SOM($J6:$W6)	text	YES
W6:W25,W28:W47	Expression	=SOM($F6:$I6)>=SOM($J6:$W6)	text	YES
X6:X25,X28:X47	Expression	=SOM($F6:$I6)<SOM($J6:$X6)	text	YES
X6:X25,X28:X47	Expression	=SOM($F6:$I6)>=SOM($J6:$X6)	text	YES
Y6:Y25,Y28:Y47	Expression	=SOM($F6:$I6)<SOM($J6:$Y6)	text	YES
Y6:Y25,Y28:Y47	Expression	=SOM($F6:$I6)>=SOM($J6:$Y6)	text	YES
Z6:Z25,Z28:Z47	Expression	=SOM($F6:$I6)<SOM($J6:$Z6)	text	YES
Z6:Z25,Z28:Z47	Expression	=SOM($F6:$I6)>=SOM($J6:$Z6)	text	YES
AA6:AA25,AA28:AA47	Expression	=SOM($F6:$I6)<SOM($J6:$AA6)	text	YES
AA6:AA25,AA28:AA47	Expression	=SOM($F6:$I6)>=SOM($J6:$AA6)	text	YES
AB6:AB25,AB28:AB47	Expression	=SOM($F6:$I6)<SOM($J6:$AB6)	text	YES
AB6:AB25,AB28:AB47	Expression	=SOM($F6:$I6)>=SOM($J6:$AB6)	text	YES
AC6:AC25,AC28:AC47	Expression	=SOM($F6:$I6)<SOM($J6:$AC6)	text	YES
AC6:AC25,AC28:AC47	Expression	=SOM($F6:$I6)>=SOM($J6:$AC6)	text	YES

Column F displays the current stock level in the warehouse of the customer
Column G displays the stock that is in transit (underway to the customer)
Column H displays the free available stock in our warehouse
Column I displays the stock which still needs to be produced

From column J onwards it shows the forecasted demand per week for the coming year.
In cells J5,K5,L5 etc. the particular weeks are displayed.

So the stock levels will be running out for each item in a certain point of time.
With conditional formatting I get sort of a visual representation of that.

I would like to build a formula which will display the particular week (as displayed in J5,K5 etc.) when the stock levels of an item will be running out.
For the second item on the list that would be "WK 18" and for the 5th item that would be "WK 14" etc.

I have been struggling with it for quite a while to no avail yet.
So I was hoping someone could push me in the right direction.

Thanks in advance for the effort

Mike

 

[SanjayGMusafir]

Check this - Column C & D

Note:

• You are using DUTCH Version thus using SOM, I'm using English so used SUM Function
• I didn't know what is your current version of Excel so Kept it very simple and used 2 helper columns
• I did nothing special just used your conditional formatting rules and put them to one formula
• Someone, better at Excel, than me can help you make this formula shorter. Of course I shall also learn during the process
• I have done the formula till column AC only you can add further as per your need until Week 52

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	ArtikelCode	EenheidCode	Stock	When short	Stock customer	Stock in transit	Stock available	Still to produce	WK02	WK03	WK04	WK05	WK06	WK07	WK08	WK09	WK10	WK11	WK12	WK13	WK14	WK15	WK16	WK17	WK18	WK19	WK20	WK21
2	L560.65.0125	KG	317		317					39		39								39	118
3	L560.65.0120	KG	814	WK18	259		555			171	132								118		277				118
4	L560.65.0122	KG	582		582				158		79								79		79
5	L560.65.0124	KG	861		861							229		229
6	L560.65.0123	KG	2717	WK14	2172		545		877		832			178				661			1024				1612
7	L560.65.0126	KG	2771		1271	1500			398	178	178			183					362		546				435
8	L560.65.0119	KG	10967	WK18	2045	5000	3922		1596	1258	178			481	178	178	1686		535		4630				893
9	L560.65.0121	KG	1679	WK18	1679													661			441				863
10	L560.65.0118	KG	799	WK14	799				143		135	32		112	32	48		32	48	60	309				393
11	L560.65.0117	KG	3443	WK18	970	2000	473		117	161	173	205	154	332	225	241	173	668	48	60	852				1153
12	L560.65.0116	KG	1503		503	1000				43		299		255						43	131
13	L560.65.0115	KG	2479	WK14	1479	1000			603	341	198			204	38	38		200	402	38	740				643
14	L560.65.0114	KG	3167	WK11	1667	1500			1135		1052			198		96	96	992	95		1137				1949
15	L560.65.0113	KG	11144	WK14	2842	5000	3302		2054	1587	345			534	237	259	1933	221	787		5448				1284
16	L560.65.0112	KG	3594		1964	815	815		175		88							734	87		577				959
17	L560.65.0109	KG	687		687
18	L560.65.0106	KG	2486	WK18	1115		1371		613					208		226		613							839
19	L560.65.0082	KG	4269		2269	2000			680					231		251		680							932
20	L560.65.0055	KG	931		707	112	112			46											27
21	L560.65.0067	KG	608		404	102	102					62	22			62				62	62				61
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=SUM(E2:H2)
D2:D21	D2	=IFS(I2>C2,$I$1,SUM(I2:J2)>C2,$J$1,SUM(I2:K2)>C2,$K$1,SUM(I2:L2)>C2,$L$1,SUM(I2:M2)>C2,$M$1,SUM(I2:N2)>C2,$N$1,SUM(I2:O2)>C2,$O$1,SUM(I2:P2)>C2,$P$1,SUM(I2:Q2)>C2,$Q$1,SUM(I2:R2)>C2,$R$1,SUM(I2:S2)>C2,$S$1,SUM(I2:T2)>C2,$T$1,SUM(I2:U2)>C2,$U$1,SUM(I2:V2)>C2,$V$1,SUM(I2:W2)>C2,$W$1,SUM(I2:X2)>C2,$X$1,SUM(I2:Y2)>C2,$Y$1,SUM(I2:Z2)>C2,$Z$1,SUM(I2:AA2)>C2,$AA$1,SUM(I2:AB2)>C2,$AB$1,SUM(I2:AC2>C2),$AC$1,TRUE,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D21	Expression	=D2<>""	text	NO

 

[MrTinkertrain]

Thank you Sanjay.
I will try your solution

 

[Fluff]

What version of Excel are you using?

I suggest that you 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’)

 

[MrTinkertrain]

At work we have Office365 and at home i'm using Office 2021 Professional

 

[SanjayGMusafir]

At work we have Office365 and at home i'm using Office 2021 Professional

Just as @Fluff suggested above in thread #4 Please update the same in your profile too, so that it is visible to everyone who visits this post to help you out.

 

[MrTinkertrain]

Good idea, guys
My bad !

 

[Fluff]

Thanks for that

Another option for 365

Fluff.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
5	ArtikelCode	EenheidCode	Stock customer	Stock in transit	Stock available	Still to produce	WK02	WK03	WK04	WK05	WK06	WK07	WK08	WK09	WK10	WK11	WK12	WK13	WK14	WK15	WK16	WK17	WK18	WK19	WK20	WK21
6	L560.65.0125	KG	317				0	39	0	39	0	0	0	0	0	0	0	39	118	0	0	0	0	0	0	0
7	L560.65.0120	KG	259		555		0	171	132	0	0	0	0	0	0	0	118	0	277	0	0	0	118	0	0	0	WK18
8	L560.65.0122	KG	582				158	0	79	0	0	0	0	0	0	0	79	0	79	0	0	0	0	0	0	0
9	L560.65.0124	KG	861				0	0	0	229	0	229	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	L560.65.0123	KG	2172		545		877	0	832	0	0	178	0	0	0	661	0	0	1024	0	0	0	1612	0	0	0	WK14
11	L560.65.0126	KG	1271	1500			398	178	178	0	0	183	0	0	0	0	362	0	546	0	0	0	435	0	0	0
12	L560.65.0119	KG	2045	5000	3922		1596	1258	178	0	0	481	178	178	1686	0	535	0	4630	0	0	0	893	0	0	0	WK18
13	L560.65.0121	KG	1679				0	0	0	0	0	0	0	0	0	661	0	0	441	0	0	0	863	0	0	0	WK18
14	L560.65.0118	KG	799				143	0	135	32	0	112	32	48	0	32	48	60	309	0	0	0	393	0	0	0	WK14
15	L560.65.0117	KG	970	2000	473		117	161	173	205	154	332	225	241	173	668	48	60	852	0	0	0	1153	0	0	0	WK18
16	L560.65.0116	KG	503	1000			0	43	0	299	0	255	0	0	0	0	0	43	131	0	0	0	0	0	0	0
17	L560.65.0115	KG	1479	1000			603	341	198	0	0	204	38	38	0	200	402	38	740	0	0	0	643	0	0	0	WK14
18	L560.65.0114	KG	1667	1500			1135	0	1052	0	0	198	0	96	96	992	95	0	1137	0	0	0	1949	0	0	0	WK11
19	L560.65.0113	KG	2842	5000	3302		2054	1587	345	0	0	534	237	259	1933	221	787	0	5448	0	0	0	1284	0	0	0	WK14
20	L560.65.0112	KG	1964	815	815		175	0	88	0	0	0	0	0	0	734	87	0	577	0	0	0	959	0	0	0
21	L560.65.0109	KG	687				0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	L560.65.0106	KG	1115		1371		613	0	0	0	0	208	0	226	0	613	0	0	0	0	0	0	839	0	0	0	WK18
23	L560.65.0082	KG	2269	2000	0		680	0	0	0	0	231	0	251	0	680	0	0	0	0	0	0	932	0	0	0
24	L560.65.0055	KG	707	112	112		0	46	0	0	0	0	0	0	0	0	0	0	27	0	0	0	0	0	0	0
25	L560.65.0067	KG	404	102	102		0	0	0	62	22	0	0	62	0	0	0	62	62	0	0	0	61	0	0	0
Main

Cell Formulas
Range	Formula
AD6:AD25	AD6	=INDEX(FILTER($J$5:$AC$5,SCAN(SUM(F6:H6),J6:AC6,LAMBDA(a,b,a-b))<0,""),1,1)

 

[MrTinkertrain]

Thank you both very much for your contributions Sanjay and Fluff
Highly appreciated
I will test a.s.a.p. and if the solutions work for me, I will mark my thread as "solved"

 

[Fluff]

Glad we could help & thanks for the feedback.