Example sheet: https://docs.google.com/spreadsheets/d/14ma-y3esh1S_EkzHpFBvLb0GzDZZiDsSVXFktH3Rr_E/edit?usp=sharing
In column B of ItemData sheet, I have achieved the result I want by copying the formula into every cell in the column, but I want to solve this using ArrayFormula instead.
In column C I have achieved the same result using ArrayFormula. However, for addition, column C is referring to cells in column B, while column B is referring to cells in column B. I.e. every cell in column B is adding 1 to the cell on the row above.
If I select the C3 formula text and paste it into the cell edit field for cell B3 (to not screw up cell references during copy - I know I could make them static references, but this is not my problem), the cell gets an error value of
#REF!
Error Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings.
Do note that the additions that need to be done are the same in both cases: Add 1 to the value of the cell on the previous row, so there is no circular reference involved. There is a starting value provided in B2, and cells in B3 and downwards should use the data from the B cell in the previous row.
Also, note that I did try File->Spreadsheet settings and enabling circular reference computation with max 25 items, but this only fills in the first two cells (B3 and B4).
How can I solve this problem? I would prefer having something like ArrayFormula, where the formula only exists in a single cell. But copy-pasting would be acceptable as long as any new rows, inserted in between or added at the bottom, would get the same formula added in column B.
Will matching items always be consecutive? It seems that way since you're comparing each Item cell to the cell above it right in your formula logic. That breaks an [unwritten?] rule of spreadsheet normalization; values' addresses themselves generally should not be treated as data.
IF you're committed to it though, have you considered explicitly using location as a data source? Example:
=ARRAYFORMULA(IFS(
NOT(LEN(A3:A40)),,
ROW(A3:A40)-3-MATCH(A3:A40,A$3:A$40,0)<=VLOOKUP(VLOOKUP(A3:A40,Items!$A$2:$D,2,false),DataPerColor!$A$2:$B,2,false),ROW(A3:A40)-3-MATCH(A3:A40,A$3:A$40,0),
true,
))
Just like your formulas, all that does in English is:
for each row,
if there's no Item, don't output any ItemData,
if the number that belongs in this cell¹ is less than or equal to the lookup, print it,
otherwise, don't output any ItemData
But then what is ¹ "the number that belongs in this cell" and how can we calculate it without using column B? I abuse locations of things to get it. Looking down your row B, each number that appears is just:
this row's number, minus
the row where items start [always 3], minus
the row number [in just the Item rows] of the first row containing this row's Item
Using the second-to-last ItemC as an example: the first ItemC is the 16th item listing, and the one we're looking up… the "second-to-last ItemC" is in row 21 of the sheet. 21-3-16 = 2 …the number you wanted.
If you can stomach that, it's a single formula and does work according to your specifications.