I am working on a report to return a value if criteria is met. I've filled in the table below for the Next Production Qty and Next Production Date that i'd like to see populated, I just need help with a formula to help me do this since there are 20-30 items per customer and dozens of customers. The idea is to pull a production date from the production data into column H in the Order Data if there is a 0 in the Balance On Hand after Order Qty/Column E. The value pulled will be dependent on a math equation, so in the first example I've pulled the first production date for that item, then the next cell, H6 would need to evaluate (g5-(c5-e4))<0, if true, pull the next match in the production data, if false pull the same match.
if there is no match show a 0. In the actual data set there will be a blank line between different items, so i'd also need to build in a way to start over where A# = "".
Thank you in advance!
<tbody>
</tbody>
if there is no match show a 0. In the actual data set there will be a blank line between different items, so i'd also need to build in a way to start over where A# = "".
Thank you in advance!
Order Data | Production Data | ||||||||||
A | B | C | D | E | G | H | A | B | C | ||
Item | Delivery Date | Open Order Qty | On Hand Qty | Balance from On Hand After Order Qty | Next Production Qty | Next Production Date | Part Description | Production Date | Production Qty | ||
1 | 749115E | 3/22/2018 | 410 | 31763 | 31353 | 1 | 749115E | 4/27/2018 | 20160 | ||
2 | 749115E | 4/27/2018 | 10080 | 21273 | 2 | 749115E | 5/25/2018 | 10080 | |||
3 | 749115E | 5/31/2018 | 10080 | 11193 | 3 | 60000000045568 | 4/27/2018 | 9936 | |||
4 | 749115E | 6/6/2018 | 10080 | 1113 | | | | ||||
5 | 749115E | 7/6/2018 | 10080 | 0 | 20160 | 4/27/2018 | | | | ||
6 | 749115E | 7/13/2018 | 10080 | 0 | 20160 | 4/27/2018 | | | | ||
7 | 749115E | 7/31/2018 | 10080 | 0 | 10080 | 5/25/2018 | | | | ||
8 | 749115E | 8/14/2018 | 10080 | 0 | 10080 | 6/22/2018 | | | | ||
9 | 60000000045568 | 4/27/2018 | 4968 | 6184 | 4618 | ||||||
10 | 60000000045568 | 5/15/2018 | 4968 | 0 | 9936 | 4/28/2018 | |||||
11 | 60000000045568 | 5/17/2018 | 4968 | 0 | 9936 | 4/28/2018 |
<tbody>
</tbody>