Excel Sheet 2:
Column D are the different part numbers we offer.
Column E is the quantity ordered on a specific Purchase order.
Column H is the quantity shipped from that specific purchase order.
Column I is the requested date from that specific purchase order.
Excel Sheet 1:
Column A is the part number
Column B is the stock levels of the different part numbers
I want to look for a specific part number (Column D) and sum all the quantities on purchase order (Column E), while subtracting what has already been shipped (Column H).
Then taking the stock levels from Excel sheet 1 ( Column B) of a specific part number (Column A) to figure out how long stock will last per purchase orders (Column I), and then return a specific date (03/06/2023).
We sell Apples, Oranges, and Bananas.
Multiple purchase orders with different due dates.
We are searching how long the Apple stock will last based on Open Sales Orders / partially invoiced Open Sales Orders.
We have 100 Apples on sales orders, with 10 already being shipped leaving 90 Apples on Sales Orders.
We have 80 Apples currently in stock.
I want to find out that one 03/06/2023 we will be short on Apples and not able to cover the sales order.
I want it to return a runout date not specifically 03/06/2023 that was just an example
This formula is close, but I need it to sum through open sales orders and then return the run out of date once the stock is not sufficient enough to cover that particular part number based on Open Orders. Find that column E doesn't have enough stock after deducting Column B from Sheet 1 to cover the open sales orders, then on the same row return Column I for the requested date shortage.
=IF(SUMIFS(Sheet2!E:E,Sheet2!D:D,A1)-SUMIFS(Sheet2!H:H,Sheet2!D:D,A1)>B1,"03/06/2023","")
