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

MrTinkertrain

Board Regular
Joined
Feb 7, 2007
Messages
66
Office Version
  1. 365
  2. 2021
Dear Excel-gurus,

I have got the following (mini)sheet

Concept VMI analyse-voorbeeld VMI.xlsx
DEFGHIJKLMNOPQRSTUVWXYZAAABAC
5ArtikelCodeEenheidCodeStock customerStock in transitStock availableStill to produceWK02WK03WK04WK05WK06WK07WK08WK09WK10WK11WK12WK13WK14WK15WK16WK17WK18WK19WK20WK21
6L560.65.0125KG317393939118
7L560.65.0120KG259555171132118277118
8L560.65.0122KG582158797979
9L560.65.0124KG861229229
10L560.65.0123KG217254587783217866110241612
11L560.65.0126KG12711500398178178183362546435
12L560.65.0119KG2045500039221596125817848117817816865354630893
13L560.65.0121KG1679661441863
14L560.65.0118KG799143135321123248324860309393
15L560.65.0117KG970200047311716117320515433222524117366848608521153
16L560.65.0116KG50310004329925543131
17L560.65.0115KG14791000603341198204383820040238740643
18L560.65.0114KG166715001135105219896969929511371949
19L560.65.0113KG28425000330220541587345534237259193322178754481284
20L560.65.0112KG19648158151758873487577959
21L560.65.0109KG687
22L560.65.0106KG11151371613208226613839
23L560.65.0082KG22692000680231251680932
24L560.65.0055KG7071121124627
25L560.65.0067KG404102102622262626261
8589000016317_200106
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J6:J25Expression=SOM($F6:$I6)<SOM($J6:$J6)textYES
J6:J25Expression=SOM($F6:$I6)>=SOM($J6:$J6)textYES
K6:K25,K28:K47Expression=SOM($F6:$I6)<SOM($J6:$K6)textYES
K6:K25,K28:K47Expression=SOM($F6:$I6)>=SOM($J6:$K6)textYES
L6:L25,L28:L47Expression=SOM($F6:$I6)<SOM($J6:$L6)textYES
L6:L25,L28:L47Expression=SOM($F6:$I6)>=SOM($J6:$L6)textYES
M6:M25,M28:M47Expression=SOM($F6:$I6)<SOM($J6:$M6)textYES
M6:M25,M28:M47Expression=SOM($F6:$I6)>=SOM($J6:$M6)textYES
N6:N25,N28:N47Expression=SOM($F6:$I6)<SOM($J6:$N6)textYES
N6:N25,N28:N47Expression=SOM($F6:$I6)>=SOM($J6:$N6)textYES
O6:O25,O28:O47Expression=SOM($F6:$I6)<SOM($J6:$O6)textYES
O6:O25,O28:O47Expression=SOM($F6:$I6)>=SOM($J6:$O6)textYES
P6:P25,P28:P47Expression=SOM($F6:$I6)<SOM($J6:$P6)textYES
P6:P25,P28:P47Expression=SOM($F6:$I6)>=SOM($J6:$P6)textYES
Q6:Q25,Q28:Q47Expression=SOM($F6:$I6)<SOM($J6:$Q6)textYES
Q6:Q25,Q28:Q47Expression=SOM($F6:$I6)>=SOM($J6:$Q6)textYES
R6:R25,R28:R47Expression=SOM($F6:$I6)<SOM($J6:$R6)textYES
R6:R25,R28:R47Expression=SOM($F6:$I6)>=SOM($J6:$R6)textYES
S6:S25,S28:S47Expression=SOM($F6:$I6)<SOM($J6:$S6)textYES
S6:S25,S28:S47Expression=SOM($F6:$I6)>=SOM($J6:$S6)textYES
T6:T25,T28:T47Expression=SOM($F6:$I6)<SOM($J6:$T6)textYES
T6:T25,T28:T47Expression=SOM($F6:$I6)>=SOM($J6:$T6)textYES
U6:U25,U28:U47Expression=SOM($F6:$I6)<SOM($J6:$U6)textYES
U6:U25,U28:U47Expression=SOM($F6:$I6)>=SOM($J6:$U6)textYES
V6:V25,V28:V47Expression=SOM($F6:$I6)<SOM($J6:$V6)textYES
V6:V25,V28:V47Expression=SOM($F6:$I6)>=SOM($J6:$V6)textYES
W6:W25,W28:W47Expression=SOM($F6:$I6)<SOM($J6:$W6)textYES
W6:W25,W28:W47Expression=SOM($F6:$I6)>=SOM($J6:$W6)textYES
X6:X25,X28:X47Expression=SOM($F6:$I6)<SOM($J6:$X6)textYES
X6:X25,X28:X47Expression=SOM($F6:$I6)>=SOM($J6:$X6)textYES
Y6:Y25,Y28:Y47Expression=SOM($F6:$I6)<SOM($J6:$Y6)textYES
Y6:Y25,Y28:Y47Expression=SOM($F6:$I6)>=SOM($J6:$Y6)textYES
Z6:Z25,Z28:Z47Expression=SOM($F6:$I6)<SOM($J6:$Z6)textYES
Z6:Z25,Z28:Z47Expression=SOM($F6:$I6)>=SOM($J6:$Z6)textYES
AA6:AA25,AA28:AA47Expression=SOM($F6:$I6)<SOM($J6:$AA6)textYES
AA6:AA25,AA28:AA47Expression=SOM($F6:$I6)>=SOM($J6:$AA6)textYES
AB6:AB25,AB28:AB47Expression=SOM($F6:$I6)<SOM($J6:$AB6)textYES
AB6:AB25,AB28:AB47Expression=SOM($F6:$I6)>=SOM($J6:$AB6)textYES
AC6:AC25,AC28:AC47Expression=SOM($F6:$I6)<SOM($J6:$AC6)textYES
AC6:AC25,AC28:AC47Expression=SOM($F6:$I6)>=SOM($J6:$AC6)textYES


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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1ArtikelCodeEenheidCodeStockWhen shortStock customerStock in transitStock availableStill to produceWK02WK03WK04WK05WK06WK07WK08WK09WK10WK11WK12WK13WK14WK15WK16WK17WK18WK19WK20WK21
2L560.65.0125KG317 317393939118
3L560.65.0120KG814WK18259555171132118277118
4L560.65.0122KG582 582158797979
5L560.65.0124KG861 861229229
6L560.65.0123KG2717WK14217254587783217866110241612
7L560.65.0126KG2771 12711500398178178183362546435
8L560.65.0119KG10967WK182045500039221596125817848117817816865354630893
9L560.65.0121KG1679WK181679661441863
10L560.65.0118KG799WK14799143135321123248324860309393
11L560.65.0117KG3443WK18970200047311716117320515433222524117366848608521153
12L560.65.0116KG1503 50310004329925543131
13L560.65.0115KG2479WK1414791000603341198204383820040238740643
14L560.65.0114KG3167WK11166715001135105219896969929511371949
15L560.65.0113KG11144WK1428425000330220541587345534237259193322178754481284
16L560.65.0112KG3594 19648158151758873487577959
17L560.65.0109KG687 687
18L560.65.0106KG2486WK1811151371613208226613839
19L560.65.0082KG4269 22692000680231251680932
20L560.65.0055KG931 7071121124627
21L560.65.0067KG608 404102102622262626261
Sheet1
Cell Formulas
RangeFormula
C2:C21C2=SUM(E2:H2)
D2:D21D2=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
CellConditionCell FormatStop If True
D2:D21Expression=D2<>""textNO
 
Upvote 0
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’)
 
Upvote 0
Thanks for that (y)

Another option for 365
Fluff.xlsm
DEFGHIJKLMNOPQRSTUVWXYZAAABACAD
5ArtikelCodeEenheidCodeStock customerStock in transitStock availableStill to produceWK02WK03WK04WK05WK06WK07WK08WK09WK10WK11WK12WK13WK14WK15WK16WK17WK18WK19WK20WK21
6L560.65.0125KG3170390390000000391180000000 
7L560.65.0120KG259555017113200000001180277000118000WK18
8L560.65.0122KG5821580790000000790790000000 
9L560.65.0124KG861000229022900000000000000 
10L560.65.0123KG21725458770832001780006610010240001612000WK14
11L560.65.0126KG127115003981781780018300003620546000435000 
12L560.65.0119KG20455000392215961258178004811781781686053504630000893000WK18
13L560.65.0121KG167900000000066100441000863000WK18
14L560.65.0118KG799143013532011232480324860309000393000WK14
15L560.65.0117KG970200047311716117320515433222524117366848608520001153000WK18
16L560.65.0116KG50310000430299025500000431310000000 
17L560.65.0115KG14791000603341198002043838020040238740000643000WK14
18L560.65.0114KG16671500113501052001980969699295011370001949000WK11
19L560.65.0113KG28425000330220541587345005342372591933221787054480001284000WK14
20L560.65.0112KG1964815815175088000000734870577000959000 
21L560.65.0109KG68700000000000000000000 
22L560.65.0106KG11151371613000020802260613000000839000WK18
23L560.65.0082KG226920000680000023102510680000000932000 
24L560.65.0055KG7071121120460000000000270000000 
25L560.65.0067KG40410210200062220062000626200061000 
Main
Cell Formulas
RangeFormula
AD6:AD25AD6=INDEX(FILTER($J$5:$AC$5,SCAN(SUM(F6:H6),J6:AC6,LAMBDA(a,b,a-b))<0,""),1,1)
 
Upvote 0
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" :)
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top