G
Guest
My company is a basic product distributor. We receive sales orders for
products from customers, and fulfill those orders with inventory that is
brought in on containers from overseas.
I am trying to write a lookup formula that will *allocate inventory* to
customers on a first-come-first-serve basis, and provide them with an order
fulfillment date.
I have two worksheets in my Excel workbook: Sales Orders and Container
Arrival. Here are the data structures:
SALES ORDERS
------------------
A B C D E F G
Item OrderID OrdDate Customer Qty RunSum ArrivDate
ProductA 103 1/1/2007 Smith 2 2 (Lookup)
ProductA 108 1/5/2007 Jones 5 7 (Lookup)
ProductA 113 1/15/2007 Blake 6 13 (Lookup)
ProductA 119 1/20/2007 Oberg 1 14 .....
ProductA 123 2/10/2007 Good 5 19
ProductB 105 1/3/2007 Thom 5 5
ProductB 108 1/5/2007 Jones 15 20
ProductB 120 1/22/2007 Hill 4 24
ProductB 123 2/10/2007 Good 2 26
CONTAINER ARRIVAL
------------------------
A B C D E
Item ContID ArrivDate Qty RunSum
ProductA WDFG 28-May 10 10
ProductA TRBN 28-May 0 10
ProductA GHWR 4-Jun 5 15
ProductB WDFG 28-May 0 0
ProductB TRBN 28-May 0 0
ProductB GHWR 4-Jun 2 2
ProductC WDFG 28-May 0 0
ProductC TRBN 28-May 53 53
ProductC GHWR 4-Jun 2 55
In the Sales Order table, the RunSum column is a Running Total of the
quantities ordered for a specific product
In the ContainerArrival table, the RunSum column is a Running Total of the
quantities arriving for a specific product.
In order to provide customers with the date their order will be fulfilled, I
need to compare the product quantities being ordered with the product
quantities coming in, and assign dates to each sales order a first-come first
serve basis.
Thus, here is what I'm trying to get the formula to do:
--Lookup the products in comumn-A of the Sales Order table on the related
Container Arrival Table (also in col-A).
--where the RunSum (col-F) of the Sales Order table is less than or equal to
(<=) the RunSum (col-E) on the Container Arrival table, return the container
arrival date (col-C on the Container Arrival table). This is the date that
that order can be fulfilled. If there is not enough inventory coming in, the
formula should generate an error message.
If the lookup formula is working correctly, it should return an arrival date
= June-4 for ProductA with OrderID 113 (on the Sales Order table).
Thank you to anyone who can end my anguish!
products from customers, and fulfill those orders with inventory that is
brought in on containers from overseas.
I am trying to write a lookup formula that will *allocate inventory* to
customers on a first-come-first-serve basis, and provide them with an order
fulfillment date.
I have two worksheets in my Excel workbook: Sales Orders and Container
Arrival. Here are the data structures:
SALES ORDERS
------------------
A B C D E F G
Item OrderID OrdDate Customer Qty RunSum ArrivDate
ProductA 103 1/1/2007 Smith 2 2 (Lookup)
ProductA 108 1/5/2007 Jones 5 7 (Lookup)
ProductA 113 1/15/2007 Blake 6 13 (Lookup)
ProductA 119 1/20/2007 Oberg 1 14 .....
ProductA 123 2/10/2007 Good 5 19
ProductB 105 1/3/2007 Thom 5 5
ProductB 108 1/5/2007 Jones 15 20
ProductB 120 1/22/2007 Hill 4 24
ProductB 123 2/10/2007 Good 2 26
CONTAINER ARRIVAL
------------------------
A B C D E
Item ContID ArrivDate Qty RunSum
ProductA WDFG 28-May 10 10
ProductA TRBN 28-May 0 10
ProductA GHWR 4-Jun 5 15
ProductB WDFG 28-May 0 0
ProductB TRBN 28-May 0 0
ProductB GHWR 4-Jun 2 2
ProductC WDFG 28-May 0 0
ProductC TRBN 28-May 53 53
ProductC GHWR 4-Jun 2 55
In the Sales Order table, the RunSum column is a Running Total of the
quantities ordered for a specific product
In the ContainerArrival table, the RunSum column is a Running Total of the
quantities arriving for a specific product.
In order to provide customers with the date their order will be fulfilled, I
need to compare the product quantities being ordered with the product
quantities coming in, and assign dates to each sales order a first-come first
serve basis.
Thus, here is what I'm trying to get the formula to do:
--Lookup the products in comumn-A of the Sales Order table on the related
Container Arrival Table (also in col-A).
--where the RunSum (col-F) of the Sales Order table is less than or equal to
(<=) the RunSum (col-E) on the Container Arrival table, return the container
arrival date (col-C on the Container Arrival table). This is the date that
that order can be fulfilled. If there is not enough inventory coming in, the
formula should generate an error message.
If the lookup formula is working correctly, it should return an arrival date
= June-4 for ProductA with OrderID 113 (on the Sales Order table).
Thank you to anyone who can end my anguish!