Dynamic Dropdown lists with Form Controls

[bluepanda]

Hi,

Would anyone know how i can create a dynamic dropdown list for a form in Excel? Where i choose an option in the 1st dropdown box, it will pull the related options tied to it. So far the ones i found from the internet mostly discuss about using data validation, but I need to do this using Form controls...

Example,
Dropdown 1 lists:
Student
Executive
Retiree

Dropdown 2
High School
Tertiary
Private Sector
Civil Servant
Own business
Not Applicable

When person A chooses from Dropdown 1 ---> Executive,
instead of seeing the entire options in Dropdown 2, he will only see Private Sector, Civil Servant and Own Business.

Or if Person B chooses from Dropdown 2 ----> Student,
He/she only sees High School and Tertiary

[HansV]

Welcome to Eileen's Lounge!

Would you like to do this using Form controls on a worksheet, or on a userform created in the Visual Basic Editor?

[bluepanda]

Hi, thanks for the warn welcome :)

As for the form, i would prefer if it could be done on a worksheet (not using data validation) but if that is not possible and VB has to be used, then VB would be appreciate too..

[HansV]

This can be done without code, by creating a dynamic named range that acts as input range for the second combo box.

In the attached sample workbook, the first combo box has fixed named range as input range: List1 refers to Sheet1!$K$2:$K$4.
Cell A2 is the linked cell of the first combo box, and this is used in the definition of List2, the input range of the second combo box.
List2 refers to

=OFFSET(Sheet1!$M$2,0,Sheet1!$A$2-1,COUNTA(OFFSET(Sheet1!$M$2:$M$25,0,Sheet1!$A$2-1)),1)

When the user selects the first item in the first dropdown, this refers to the non-blank part of M2:M25. When the user selects the second item, it refers to the non-blank part of N2:N25 etc.

[bluepanda]

aahh, now i understand how it works...

Thanks for the explanation!!