Forum Discussion
Formula Help
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 on 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","")
Your formula is close but not exactly correct. You need to use a different approach to find the run out date based on stock and orders.
One possible solution is to use a helper column that calculates the cumulative net orders for each part number (Column E minus Column H), and then use a lookup function to find the first date (Column I) that matches or exceeds the stock level (Sheet 1 Column B) for that part number (Sheet 1 Column A).
For example, you can add a new column J in Sheet 2 with this formula in J2 and copy it down:
=SUMIFS(E$2:E2,D$2:D2,D2)-SUMIFS(H$2:H2,D$2:D2,D2)
This will give you the cumulative net orders for each part number.
Then, in Sheet 1, you can use this formula in C1 and copy it down:
=IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)
This will return the first date that matches or exceeds the stock level for each part number, or a blank if there is no such date.
I hope this helps
...is just a suggested solution, if it doesn't fit your plans, please just ignore it :).
- NikolinoDE I added the first formula into sheet 2,
I then added the formula =IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)
to sheet 1.
It keeps returning 1/0/1900
Does the lookup function generate automatically?
I tried this but keep getting an error.
=lookup("=IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)")
How does it read the request dates from Sheet two?
I appreciate your help anything further you can assist with is greatly appreciated!Did you format the cells as numbers?
This can maybe be, because Excel treats blank cells as zero values when performing calculations, and 1/0/1900 is how Excel displays zero dates.
One possible solution is to use an IF function to check if the cell is blank before applying the lookup function.
For example, you can try something like this:
=IF(ISBLANK(Sheet2!A1),“”,IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”))
This formula will return an empty string if Sheet2!A1 is blank, otherwise it will perform the lookup function as usual.
