Create Spreadsheet with Dynamic Data Population Capabilities

Renata830 ,,,

Since you have not shared any sample data therefore, I would like to use data I've recently used for project to create Dynamic Drop down, which is driven by array ( CSE ) formula.

How it works:

• Find the source data in range A2:C20, has 2 main building type, has their 3 Entry & Size (color applied for visualization ).

• An Array (CSE) formula in cell E2, creates unique list of Building Type.

  {=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$20),0)),"")}

• N.B. Create Drop Down in cell E13 and use E2:E3 as List value.

  ---

  • An array (CSE) formula in G2, gets relates Entry Type value.

     {=IFERROR(INDEX($B$2:$B$20, MATCH(0, IF($E$13=$A$2:$A$20, COUNTIF($G$1:$G1, $B$2:$B$20), ""), 0)),"")}

  N.B. Create Drop Down in cell G13 and use G2:G3 as List value.

  ---

  • Cell H2 has an array (CSE) formula, gets related Overall Size.

    {=IFERROR(INDEX($C$2:$C$20, SMALL(IF(COUNTIF($E$13, $A$2:$A$20)*COUNTIF($G$13, $B$2:$B$20), ROW($A$2:$C$20)-MIN(ROW($A$2:$C$20))+1), ROW(A1)), COLUMN(A1)),"")}

  N.B. Create Drop Down in cell H13 and use H2:H10 as List value.

  ---

  • Finish array formula with Ctrl+Shift+Enter & fill down.
  • You may adjust cell references in formula and Drop Down List value as needed.
  • Formula returns Entry Type & Overall Size, depends on the data, at source in range A2:C20.
  • For neatness you may hide data appearing in E2:H10.

N.B.  Replace shown data set with yours, and apply the array formula as been instructed.