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
Dynamic Dropdown lists with Form Controls
-
- NewLounger
- Posts: 5
- Joined: 11 Dec 2011, 02:01
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic Dropdown lists with Form Controls
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?
Would you like to do this using Form controls on a worksheet, or on a userform created in the Visual Basic Editor?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 11 Dec 2011, 02:01
Re: Dynamic Dropdown lists with Form Controls
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..
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..
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic Dropdown lists with Form Controls
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 11 Dec 2011, 02:01
Re: Dynamic Dropdown lists with Form Controls
aahh, now i understand how it works...
Thanks for the explanation!!
Thanks for the explanation!!