excel formula to return sales orders and purchase stock

Mulon

New Member
Joined
Oct 13, 2018
Messages
1
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

Sales Order#Product NameOrder DateShipped DateQuantity
20001Summer 2017, Unique, Small White10/17/201710/18/201715
20001Summer 2017, Unique, Small Black10/17/201710/19/201715
20002Summer 2017, Unique, Medium Black10/21/201710/24/20175
20003Summer 2017, Unique, Small Black10/21/201710/24/201715
20004Summer 2017, Unique, Medium Black10/23/201710/24/20175
20004Summer 2017, Unique, Large Black10/23/201710/24/20175
20005Summer 2017, Unique, Medium White10/27/201712
20006Summer 2017, Unique, Small Black10/28/20174
20006Summer 2017, Unique, Small White10/28/20174
Summer 2017, Unique, Small White11/5/201711/7/201725
Summer 2017, Unique, Small White11/25/201711/27/201725
Summer 2017, Unique, Small White12/17/201712/17/201725
Summer 2017, Unique, Small White1/4/20181/6/201825
Summer 2017, Unique, Small White1/24/20181/26/201825
Summer 2017, Unique, Small White2/13/20182/15/201825
Summer 2017, Unique, Small White3/4/20183/6/201825
Summer 2017, Unique, Small White3/24/20183/26/201825
Summer 2017, Unique, Small White4/13/20184/15/201825
Summer 2017, Unique, Small White5/3/20185/5/201825
Summer 2017, Unique, Small White5/23/20185/25/201825
Summer 2017, Unique, Small White6/12/20186/14/201825

<tbody>
</tbody>


Pur sheet

Purchase Order#Product NamePurchase DateStock
Due Date
Quantity
10001Summer 2017, Unique, Small White10/5/201710/12/201740
10001Summer 2017, Unique, Medium White10/5/201710/12/201735
10001Summer 2017, Unique, Large White10/5/201710/12/201735
10001Summer 2017, Unique, Small Black10/5/201710/12/201720
10001Summer 2017, Unique, Medium Black10/5/201710/12/201720
10001Summer 2017, Unique, Large Black10/5/201710/12/201720
10002Summer 2017, Unique, Small Black10/27/201715
10002Summer 2017, Unique, Medium Black10/27/201715
10002Summer 2017, Unique, Small White10/27/201732
Summer 2017, Unique, Small White11/26/201712/4/201736
Summer 2017, Unique, Small White11/26/20171/3/201832
Summer 2017, Unique, Small White1/25/20182/2/201836
Summer 2017, Unique, Small White2/24/20183/3/201832
Summer 2017, Unique, Small White3/25/20184/2/201836
Summer 2017, Unique, Small White4/24/20185/2/201832
Summer 2017, Unique, Small White5/24/20186/1/201836
Summer 2017, Unique, Small White6/23/20187/1/201832
Summer 2017, Unique, Small White6/24/20187/2/201824

<tbody>
</tbody>


StockInventory sheet

Product NameOrders waiting to be fufilledStock waiting to be receivedStock 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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,109,449
Messages
5,528,816
Members
409,838
Latest member
Cowells01
Top