Hello loverly people, kindly help me with a formula that can return orders waiting to be fulfilled from the sales sheet into the stockinventory sheet
And a formula that returns the stock qty waiting to be received in the stockinventory sheet from the pur sheet
and then i also want to be able to add the stock waiting to be received to stock in hand once it is shipped.
Sales sheet
<tbody>
</tbody>
Pur sheet
<tbody>
</tbody>
StockInventory sheet
<tbody>
</tbody>
thank you in advance
And a formula that returns the stock qty waiting to be received in the stockinventory sheet from the pur sheet
and then i also want to be able to add the stock waiting to be received to stock in hand once it is shipped.
Sales sheet
Sales Order# | Product Name | Order Date | Shipped Date | Quantity |
20001 | Summer 2017, Unique, Small White | 10/17/2017 | 10/18/2017 | 15 |
20001 | Summer 2017, Unique, Small Black | 10/17/2017 | 10/19/2017 | 15 |
20002 | Summer 2017, Unique, Medium Black | 10/21/2017 | 10/24/2017 | 5 |
20003 | Summer 2017, Unique, Small Black | 10/21/2017 | 10/24/2017 | 15 |
20004 | Summer 2017, Unique, Medium Black | 10/23/2017 | 10/24/2017 | 5 |
20004 | Summer 2017, Unique, Large Black | 10/23/2017 | 10/24/2017 | 5 |
20005 | Summer 2017, Unique, Medium White | 10/27/2017 | 12 | |
20006 | Summer 2017, Unique, Small Black | 10/28/2017 | 4 | |
20006 | Summer 2017, Unique, Small White | 10/28/2017 | 4 | |
Summer 2017, Unique, Small White | 11/5/2017 | 11/7/2017 | 25 | |
Summer 2017, Unique, Small White | 11/25/2017 | 11/27/2017 | 25 | |
Summer 2017, Unique, Small White | 12/17/2017 | 12/17/2017 | 25 | |
Summer 2017, Unique, Small White | 1/4/2018 | 1/6/2018 | 25 | |
Summer 2017, Unique, Small White | 1/24/2018 | 1/26/2018 | 25 | |
Summer 2017, Unique, Small White | 2/13/2018 | 2/15/2018 | 25 | |
Summer 2017, Unique, Small White | 3/4/2018 | 3/6/2018 | 25 | |
Summer 2017, Unique, Small White | 3/24/2018 | 3/26/2018 | 25 | |
Summer 2017, Unique, Small White | 4/13/2018 | 4/15/2018 | 25 | |
Summer 2017, Unique, Small White | 5/3/2018 | 5/5/2018 | 25 | |
Summer 2017, Unique, Small White | 5/23/2018 | 5/25/2018 | 25 | |
Summer 2017, Unique, Small White | 6/12/2018 | 6/14/2018 | 25 |
<tbody>
</tbody>
Pur sheet
Purchase Order# | Product Name | Purchase Date | Stock Due Date | Quantity |
10001 | Summer 2017, Unique, Small White | 10/5/2017 | 10/12/2017 | 40 |
10001 | Summer 2017, Unique, Medium White | 10/5/2017 | 10/12/2017 | 35 |
10001 | Summer 2017, Unique, Large White | 10/5/2017 | 10/12/2017 | 35 |
10001 | Summer 2017, Unique, Small Black | 10/5/2017 | 10/12/2017 | 20 |
10001 | Summer 2017, Unique, Medium Black | 10/5/2017 | 10/12/2017 | 20 |
10001 | Summer 2017, Unique, Large Black | 10/5/2017 | 10/12/2017 | 20 |
10002 | Summer 2017, Unique, Small Black | 10/27/2017 | 15 | |
10002 | Summer 2017, Unique, Medium Black | 10/27/2017 | 15 | |
10002 | Summer 2017, Unique, Small White | 10/27/2017 | 32 | |
Summer 2017, Unique, Small White | 11/26/2017 | 12/4/2017 | 36 | |
Summer 2017, Unique, Small White | 11/26/2017 | 1/3/2018 | 32 | |
Summer 2017, Unique, Small White | 1/25/2018 | 2/2/2018 | 36 | |
Summer 2017, Unique, Small White | 2/24/2018 | 3/3/2018 | 32 | |
Summer 2017, Unique, Small White | 3/25/2018 | 4/2/2018 | 36 | |
Summer 2017, Unique, Small White | 4/24/2018 | 5/2/2018 | 32 | |
Summer 2017, Unique, Small White | 5/24/2018 | 6/1/2018 | 36 | |
Summer 2017, Unique, Small White | 6/23/2018 | 7/1/2018 | 32 | |
Summer 2017, Unique, Small White | 6/24/2018 | 7/2/2018 | 24 |
<tbody>
</tbody>
StockInventory sheet
Product Name | Orders waiting to be fufilled | Stock waiting to be received | Stock on hand |
Summer 2017, Unique, Small White | |||
Summer 2017, Unique, Medium White | |||
Summer 2017, Unique, Large White | |||
Summer 2017, Unique, Small Black | |||
Summer 2017, Unique, Medium Black | |||
Summer 2017, Unique, Large Black |
<tbody>
</tbody>
thank you in advance