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