Run out Date Formula

Jacko320 

I notice now that your column headers for "Open Order # 3 Date" and "Open Order # 4 Quantity" are missing. That error can be avoided if you replace all those open order column headers with Excel formulas. So as long as the Quantity and Date values begin in column G, you can use this formula:

=LET( ColNum, COLUMN(), OrderNum, FLOOR.MATH((ColNum-5)/2),
"Open Order # " & OrderNum & IF(ISODD(ColNum)," Quantity"," Date") )

I put information on the LET function into the _Info worksheet of the attached, updated workbook. With most built-in Excel functions, Microsoft determines the calculations that occur. With the LET and LAMBDA functions, you (the author) determine what calculations are performed.

And that relates to your question about the p's and c's. The first p in Hecatonchire's LET function is a variable name, a term familiar to programmers. The person writing the formula decides what calculation is made using that variable. The variable is used within that instance of the LET function only. (But the same variable name can be used in other formulas, with perhaps a different meaning there). The variable's value (a number or text value or cell reference or range reference or…) is specified by the second parameter, optionally followed by a third parameter as a second variable name, a fourth parameter as the value of the second variable, etc. The last parameter for the LET function will be an expression (either a calculation or a repetition of a variable name) that Excel should use as the value for the cell (or for calculation, if the LET function is wrapped inside other calculations).

The c inside the LAMBDA function is like a variable, but instead of its value being determined within that LAMBDA function, its value is determined by whatever expression calls (invokes) that LAMBDA function.

Now, to some specifics. If you have my LAMBDA formula defined in the Name Manager with the name CheckForRunOutR, then the formula for cell D2 (or any available cell in row 2) is shown on worksheet "data from JB":

=CheckForRunOutR(C2,G2,H2)
(C2 is the cell it is to use for Remaining, G2 is the cell is the cell it is to use for NextQty, and H2 is the cell it is to use for NextDt.)

Copy it down as needed.

As for Hecatonchire's formula, for it to work with your data layout, you have to use the ISODD function instead of the ISEVEN function. The formula's p could instead be named OpenOrderDataForSKU (a bit more typing, but it adds meaning). See the "data from JB" and _Info worksheets, respectively, for the example and for more information. To use this formula, you would need to change the reference to column AS (or AT or whatever) to be a reference to the last column in which you will put open order data – that's column OP, if you are going to support 200 open orders. This formula is more difficult to understand than mine, but it does not involve the Name Manager, and it may calculate results faster. OTOH, you are limited in what you can display if no run out is projected.