formula that identifies specific cell value

Occasional Visitor

need a formula that can identify one specific shortage date based on warehouse stock on hand when it gets contrasted against open orders, thank you in advance for your support


1 Reply


In the attached version of your file, I inserted a Cumulative column to obtain the cumulative sum of the Order Qty per Fruit Type in the Material Handling sheet. Then, the formula in E7 of the Draft sheet, copied down rows, is: 

=IFERROR(AGGREGATE(15,6,'Material Handling'!D$5:D$30/
('Material Handling'!A$5:A$30=A7)/
('Material Handling'!C$5:C$30>B7),1),"")