Forum Discussion

Copper Contributor

Oct 07, 2023

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 ...

excel

office 365

OliverScheurich
Oct 07, 2023
bozhkov
=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.

OliverScheurich

Gold Contributor

Oct 07, 2023

bozhkov

=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.