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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You must log in or register to reply here.

Similar threads

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
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
575
Peter_SSs
Peter_SSs
T
  • Question Question
Multiple Search Criteria in Excel Table
Replies
4
Views
127
KOKOSEK
KOKOSEK
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
368
harzer
H
P
  • Solved
Transpose with dates problem - from the dd/mm/yy to mm/dd/yyyy
Replies
4
Views
589
Alex Blakenburg
A

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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