![]() ![]() I put information on the LET function into the _Info worksheet of the attached, updated workbook. "Open Order # " & OrderNum & IF(ISODD(ColNum)," Quantity"," Date") ) 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), ![]() That error can be avoided if you replace all those open order column headers with Excel formulas. I believe it would be easy to change the LAMBDA function above to accommodate that notice now that your column headers for "Open Order # 3 Date" and "Open Order # 4 Quantity" are missing. Regarding your data design: Is it a significant problem to maintain the data in the layout you showed? I would have put the open order occurrences into their own rows (not their own columns) in a separate worksheet. But I do not expect problems (other than slow computations) with many more columns of open orders. ![]() The function permits a zero OO Qty (NextQty>=0, rather than NextQty>0) to allow you to test a what-if-an-order-is-cancelled condition manually with ease.Īs in the attached workbook, I have not tested it beyond twenty open orders. a non-integer quantity was specified where an integer was required (My formula would not know which SKUs require an integer quantity.)īut I did include logic to flag a missing or nonnumeric or negative OO Qty if the OO Date is present.the initial quantity is zero or negative, or.the Open Order Dates are not in chronological order, or.Note that I did not include logic to handle any cases where: Alternatively, you could have it just output "-none-" or another text literal or a date-in-the-far-future literal. If there will be no runout with the open orders, I have the function prepend ">" to the last date. IF(Remaining-NextQty"&TEXT(OFFSET(NextDt,0,-2),"mm/dd/yy")Īll those extra spaces and soft line feeds are just present to make it easier to read and understand. Feel free to use a different name, but remember to change it inside the LAMBDA formula (as well as the formula name definition, and in cell formulas). I have created the function CheckForRunOutR (the R just reminds me that it is for checking data chronologically to the right). (A function that calls itself is called a recursive function.) If your version of Excel supports the LAMBDA function, you can define a LAMBDA function that does a calculation and conditionally (i.e., if run out has not occurred) calls itself with different arguments. So until recent years, you would have to either add many helper cells for the calculations, or calculate it in a VBA procedure (or possibly in Office Script, with which I am not familiar). The calculation must iterate over the pairs of quantity and date cells until, say, it finds an Open Order Date cell that does not contain a date. (And it would be as ugly as heck to write and debug if it did work.) The IF function can only be nested 64 levels, using that (a "brute force" technique) won't meet your needs. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |