Formula help please.

Archie Way

New Member
Joined
Aug 17, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
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

Main tab.PNG



Purchased tab.PNG



Manufactured tab.PNG
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I've been away for a bit, so I don't know if you still need this, but maybe:

Book1
ABCDEFG
1Item CodeDescriptionCategoryStockOrdersActual Lead TimeSystem lead time (days)
2RA100Ceiling lightPurchased2211022
3RA200Wall lightPurchased08118
4RA300Desk lightManufactured108010
5RA400Bathroom lightManufactured011212
Main
Cell Formulas
RangeFormula
F2:F5F2=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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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