Formula help please.

[Archie Way]

Hello,

I hope you can help me figure out a formula issue I am having.

On my spreadsheet I have a tab called 'main' which shows item code, description, category, current stock figures, quantity on customer orders, a column where I'd like my formula to go and last column is standard lead time in days.

I would like the formula to say:
If stock minus orders is greater than 0, then show 0.
If stock minus orders is less than 0 then if it is purchased (column C), look on the purchased tab, find the first purchase order quantity for that item and add it to stock then minus orders. If this is greater than 0 then calculate the number of working days between today and PO date in column E. If it is less than 0, see if there is another PO line for that item and if yes add the quantity to stock + previous PO line - orders. If this is greater than 0 then show the number of working days between today and that second PO delivery date. Repeat until you get a number greater than 0 or if it is still negative and there are no more PO lines then show the standard lead time on the main tab in column G.

If the item is manufactured in column C of the main tab and if stock minus orders is greater than 0 then show 0.
If stock minus orders is less than 0 then go to manufactured tab. Look up the item code against column F. If column G (sub assembly stock) is greater than 0 then add that figure to stock in column D on main tab minus orders in column E on main tab. If this is greater than zero then show 0.
If less than zero see if there is a works order for the item on the manufactured tab then add that work order quantity to stock in column D on main tab + stock in column G on manufactured tab minus orders in column E on main tab. If this is greater than 0 then calculate number of work days between today and works order date. If less than 0, repeat process but add the quantity from the next works orders. If that is greater than zero then show the number of work days between today and that works order date. If there are not enough / any works orders to make the number greater than 0 then show the standard lead time in column G on main tab.

This is really bugging me so any help would be gratefully received.

Thank you.
AW

 

[Eric W]

I've been away for a bit, so I don't know if you still need this, but maybe:

Book1
	A	B	C	D	E	F	G
1	Item Code	Description	Category	Stock	Orders	Actual Lead Time	System lead time (days)
2	RA100	Ceiling light	Purchased	2	21	10	22
3	RA200	Wall light	Purchased	0	8	11	8
4	RA300	Desk light	Manufactured	10	8	0	10
5	RA400	Bathroom light	Manufactured	0	1	12	12
Main

Cell Formulas
Range	Formula
F2:F5	F2	=LET(p,C2="Purchased",a,IF(p,FILTER(Purchased!$A$2:$G$100,Purchased!$B$2:$B$100=A2),FILTER(Manufactured!$A$2:$G$100,Manufactured!$F$2:$F$100=A2)),q,FILTER(a,IF(p,{0,0,0,1,0,0,0},{0,0,0,0,0,0,1})),d,FILTER(a,{0,0,0,0,1,0,0}),r,IFERROR(ROWS(a),0),v,SEQUENCE(r),h,SEQUENCE(,r),m,MMULT(h^0,IF(h>=v,q,0)),s,SEQUENCE(r+2,,0),xa,SWITCH(s,0,0,r+1,9999,INDEX(m,s))+D2,xb,SWITCH(s,0,TODAY(),r+1,TODAY()+G2,INDEX(d,s)),XLOOKUP(E2,xa,xb,"error",1)-TODAY())

I believe this works right for the Purchased items. It works the exact same way for Manufactured items, except using columns E,F,G instead of A,D,E. In reading your description over several times, it appears there's some difference between the quantity and the stock of subassembly code, so I'm probably not doing it right. I couldn't figure it out. In any event, I think this formula is more of a novelty than a serious formula. It's way too complicated to maintain easily. I think a UDF could be written that would be much easier to understand.

Anyway, hope this helps.