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

 

[pecoflyer]

Hi
please post a sample sheet, pictures are quite useless. Also add some manually calculated expected results. Thx

 

[GraH - Guido]

Archie Way,
That's a whole lot of IFs you got there, including a couple "repeat until". The latter sound to me like a recursive calculation is required. Meaning only if you are on 365 and LAMBDA () is on board, it potentially can be done without setting "iterative calculation" on. Without that option being open VBA might be advised here, I guess.

LAMBDA was made available recently (to most of us, so it's relatively speaking) and though I have been experimenting with it (be it without much of success so far), in particular the recursive part is a hard nut to crack. On this forum only a few members have displayed true mastery. So be patient for one of them to pick it up. Or add a comment if VBA is fine too.

Either way, looking at the sample data from your pictures, there are a lot of questions unanswered. I suspect there are already formulas in play, that might "interfere" with the logics you've explained. The sample seems to small to ensure a working formula in all scenarios explained in the logic.

PS: You could try on your own, by breaking down the logic in small parts and add many helper columns. This is often a better tactic over mega formulae when updates or debugging is required btw.