How to populate values in a drop-down list dynamically

Question

I have the below source data(the original data has thousands of rows)

Form  field 
Form1 field11
Form1 field12
Form2 field21
Form2 field22

In a separate sheet, I want to have 2 dropdown lists in field A1 and B1.

The drop-down list in A1 should populate the list of forms eg Form1, Form2 ,etc.

The drop-down list on B1 should populate the field based on selection from A1.

For ex if Form 1 is selected in A1 ,B1 should populate field 11 and field 12 in drop-down list.

@wils I could not find any solution hence seeking solutions here — Brian, Jun 30 '21 at 12:52
[Excel dependent dropdown lists (With/without VBA)](https://stackoverflow.com/questions/6151165/excel-dependent-dropdown-lists-with-without-vba) — Wils Mils, Jun 30 '21 at 12:52
@wils thanks for sharing the link. However this solution wouldn't work for my data as i would need to create individual columns for each form. — Brian, Jun 30 '21 at 13:01
Please show the initial code that you have done so far to be fair with others who were required the same condition. — Wils Mils, Jun 30 '21 at 13:07

score 0 · Answer 1 · answered Jul 08 '21 at 01:00

screenshots (in reverse order) and function (1st step, bottom)/sheet here refer:

4] Result

3] Validation list 'selected cells' relies upon named range:

(make sure to include # at end to extend to bottom of spill range - assumes you have Excel as part of Office 365 package)

2] Additional spill range specified in cell F4 as follows:

1] Filter function:

=FILTER(E4:E7,D4:D7=H3)

1 Answers1