SUNCIOUS
New Member
Hi all,
I need some help with my outstanding order report.
Inside my excel sheet, I need a formula to allocate available incoming stock from my inventory to specific order lines, allocating the stock based on the Order No.
The reason for requesting specific Order No is due to item like "Screw" is common part used in different customer's models which is represented by the Order No. followed by Model A or B. If no stock, the result would be 0.
However, there is also another problem as Order Line is not available under Sheet 2 and there may be a few lines under the same Order ID with the same item, only with different request delivery dates.
Thanks in advance!
Example:
Sheet 1-
A1 B1 C1 D1 E1 F1
Order No Order Line Req del date Item Order Qty Result
1001/A 1 15-May-20 Screw 20 20
1001/A 2 01-Jun-20 Screw 50 50
1001/A 3 05-Jun-20 Screw 10 0
1002/A 1 15-Jun-20 Resistor 10 5
1003/A 1 01-Jul-20 Capacitor 5 0
1004/B 1 15-Jul-20 LED 3 3
1005/A 1 05-Jun-20 Resistor 6 6
1006/B 1 05-Jun-20 Screw 4 2
Sheet 2 -
A1 B1 C1
Order No Part Incoming Qty
1001/A Screw 20
1002/A Resistor 5
1004/A LED 3
1005/A Resistor 6
1001/A Screw 50
1006/B Screw 2
I need some help with my outstanding order report.
Inside my excel sheet, I need a formula to allocate available incoming stock from my inventory to specific order lines, allocating the stock based on the Order No.
The reason for requesting specific Order No is due to item like "Screw" is common part used in different customer's models which is represented by the Order No. followed by Model A or B. If no stock, the result would be 0.
However, there is also another problem as Order Line is not available under Sheet 2 and there may be a few lines under the same Order ID with the same item, only with different request delivery dates.
Thanks in advance!
Example:
Sheet 1-
A1 B1 C1 D1 E1 F1
Order No Order Line Req del date Item Order Qty Result
1001/A 1 15-May-20 Screw 20 20
1001/A 2 01-Jun-20 Screw 50 50
1001/A 3 05-Jun-20 Screw 10 0
1002/A 1 15-Jun-20 Resistor 10 5
1003/A 1 01-Jul-20 Capacitor 5 0
1004/B 1 15-Jul-20 LED 3 3
1005/A 1 05-Jun-20 Resistor 6 6
1006/B 1 05-Jun-20 Screw 4 2
Sheet 2 -
A1 B1 C1
Order No Part Incoming Qty
1001/A Screw 20
1002/A Resistor 5
1004/A LED 3
1005/A Resistor 6
1001/A Screw 50
1006/B Screw 2