In the attached sheet I want formula in column C that will give the new order quantity with following logic;

If ("Product"="Product")of column A & I, ("Closing Stock" of a particular product<"ROL" of that product) & (Current "Date" (say date in B5 if formula is being applied in C5)-Any latest previous "New Order" Date>="Lead Time" of that product)

Means if it has given new order quantity once then it should not give it again within the lead time even though the closing stock is less than the ROL. For example if it gives 150 in C3 then 150 comes on or after 21.03.2014.

Our formula should perform the 3rd logic only if it finds any new order qty in Array C otherwise first 2 logic are enough.

Thanks in anticipation.

Attached Files

Sample Sheet.xlsx (11.1 KB, 12 views)