Forum Discussion
Solved
Dynamically filling formulas in an array
Hi! I dynamically create a product availability table from a list. Products can be added and removed from the list; the range of availability formulas needs to change with the size of the table. G2 ...
=LET(cols,TOROW(UNIQUE(Tabelle1[color])),rows,UNIQUE(Tabelle1[car]&" "&Tabelle1[seller]),MAKEARRAY(COUNTA(rows),COUNTA(cols),LAMBDA(r,c,SUM((Tabelle1[car]&" "&Tabelle1[seller]=INDEX(rows,r))*(Tabelle1[color]=INDEX(cols,c))*Tabelle1[qty]))))
Alternatively you can create a dynamic table and use the above formula. The name of the table in this example is Tabelle1.
=TOROW(UNIQUE(Tabelle1[color]))
This formula is in cell G1.
=UNIQUE(Tabelle1[car]&" "&Tabelle1[seller])
This formula is in cell F2.
In G2:
=IF(OR($F2="", G$1=""), "", LET(qty, SUMPRODUCT(($A$2:$A$1000&" "&$C$2:$C$1000=$F2)*($B$2:$B$1000=G$1), $D$2:$D$1000), IF(qty>0, qty, "")))
Fill down to for example row 100, then to the right to column CZ (or vice versa).