excel formula to return sales orders and purchase stock

M

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

[TABLE="width: 594"]
<tbody>[TR]
[TD]Sales Order#[/TD]
[TD]Product Name[/TD]
[TD]Order Date[/TD]
[TD]Shipped Date[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD="align: right"]20001[/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]10/17/2017[/TD]
[TD="align: right"]10/18/2017[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]20001[/TD]
[TD]Summer 2017, Unique, Small Black[/TD]
[TD="align: right"]10/17/2017[/TD]
[TD="align: right"]10/19/2017[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]20002[/TD]
[TD]Summer 2017, Unique, Medium Black[/TD]
[TD="align: right"]10/21/2017[/TD]
[TD="align: right"]10/24/2017[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20003[/TD]
[TD]Summer 2017, Unique, Small Black[/TD]
[TD="align: right"]10/21/2017[/TD]
[TD="align: right"]10/24/2017[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]20004[/TD]
[TD]Summer 2017, Unique, Medium Black[/TD]
[TD="align: right"]10/23/2017[/TD]
[TD="align: right"]10/24/2017[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20004[/TD]
[TD]Summer 2017, Unique, Large Black[/TD]
[TD="align: right"]10/23/2017[/TD]
[TD="align: right"]10/24/2017[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20005[/TD]
[TD]Summer 2017, Unique, Medium White[/TD]
[TD="align: right"]10/27/2017[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]20006[/TD]
[TD]Summer 2017, Unique, Small Black[/TD]
[TD="align: right"]10/28/2017[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]20006[/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]10/28/2017[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]11/5/2017[/TD]
[TD="align: right"]11/7/2017[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]11/25/2017[/TD]
[TD="align: right"]11/27/2017[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]12/17/2017[/TD]
[TD="align: right"]12/17/2017[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]1/6/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]1/24/2018[/TD]
[TD="align: right"]1/26/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]2/13/2018[/TD]
[TD="align: right"]2/15/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]3/4/2018[/TD]
[TD="align: right"]3/6/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]3/24/2018[/TD]
[TD="align: right"]3/26/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]4/13/2018[/TD]
[TD="align: right"]4/15/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]5/3/2018[/TD]
[TD="align: right"]5/5/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]5/23/2018[/TD]
[TD="align: right"]5/25/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]6/12/2018[/TD]
[TD="align: right"]6/14/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]


Pur sheet

[TABLE="width: 570"]
<tbody>[TR]
[TD]Purchase Order#[/TD]
[TD]Product Name[/TD]
[TD]Purchase Date[/TD]
[TD]Stock
Due Date[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD="align: right"]10/12/2017[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Summer 2017, Unique, Medium White[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD="align: right"]10/12/2017[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Summer 2017, Unique, Large White[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD="align: right"]10/12/2017[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Summer 2017, Unique, Small Black[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD="align: right"]10/12/2017[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Summer 2017, Unique, Medium Black[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD="align: right"]10/12/2017[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Summer 2017, Unique, Large Black[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD="align: right"]10/12/2017[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD]Summer 2017, Unique, Small Black[/TD]
[TD="align: right"]10/27/2017[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD]Summer 2017, Unique, Medium Black[/TD]
[TD="align: right"]10/27/2017[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]10/27/2017[/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]11/26/2017[/TD]
[TD="align: right"]12/4/2017[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]11/26/2017[/TD]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]1/25/2018[/TD]
[TD="align: right"]2/2/2018[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]2/24/2018[/TD]
[TD="align: right"]3/3/2018[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]3/25/2018[/TD]
[TD="align: right"]4/2/2018[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]4/24/2018[/TD]
[TD="align: right"]5/2/2018[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]5/24/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]6/23/2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer 2017, Unique, Small White[/TD]
[TD="align: right"]6/24/2018[/TD]
[TD="align: right"]7/2/2018[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]


StockInventory sheet

[TABLE="width: 553"]
<tbody>[TR]
[TD]Product Name[/TD]
[TD]Orders waiting to be fufilled[/TD]
[TD]Stock waiting to be received[/TD]
[TD]Stock on hand[/TD]
[/TR]
[TR]
[TD]Summer 2017, Unique, Small White[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summer 2017, Unique, Medium White[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summer 2017, Unique, Large White[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summer 2017, Unique, Small Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summer 2017, Unique, Medium Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summer 2017, Unique, Large Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


thank you in advance
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You must log in or register to reply here.

Similar threads

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
586
Peter_SSs
Peter_SSs
T
  • Question Question
Multiple Search Criteria in Excel Table
Replies
4
Views
137
KOKOSEK
KOKOSEK
B
  • Question Question
Grouping people by demographics and survey answers into 3 groups.
Replies
5
Views
120
Kaper
Kaper
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
372
harzer
H

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top