Forum Discussion
Create Spreadsheet with Dynamic Data Population Capabilities
I want to have my spreadsheet column populate dynamic upon a drop down selection. The choice in the drop down will fill in other pertinent data across the row within appropriate columns. Is this a capability and if so. Can someone walk me through how to do it? Thanks in advance
13 Replies
- Yes it's very much possible,,, if I'm not wrong I can say that you are in need of Dependent drop down,, please share some sample data & expected output, will help us to fix the issue.
Rajesh_Sinha - forward you a sample spreadsheet of what I am looking to do. Thank you for the assistance/direction.
Best,
Renata
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.
- it can be done, can you be more specific?
Ramiz_Assaf - Hi Ramiz -- thank you for responding.
I want to create a drop down list and depending on what is selected. the corresponding columns will be populated with pre-defined values...in this case a simple X to identify the need for that function to review.
I have 150 types for which each has 4 possible selections (the dependent list). Depending on what is selected from the 4 possible selections it will indicate across columns which functional area is impacted by simply placing an "X" in the corresponding cell within the row.
