Forum Discussion
Multiple dropdowns using shared terms
Hi,
I'm trying to create an interview form in Excel with multiple dropdowns. Essentially, if a certain role type is selected, I need the form to bring up a set of questions related to a particular competency. The issue is that the competencies are the same regardless of the role type but the questions within the competency change depending on the role type. There are ten total competencies.
For example, if the role "Director" is selected and the competency "Accountability" is then selected, a specific set of questions will appear in the dropdown. If the role is "Manager" and the competency is "Accountability", a different set of questions will appear in the dropdown.
The issue is that I can only have one group named "Accountability" to reference via indirect Data Validation. I realize that I can technically create variations of the group name "Accountability" for each role type, but I only want the end user to see the "Accountability" in the dropdown menu.
What is the best way to do this?
Additionally, if the competency has a space, e.g. "Learning Agility", how do I make it appear that way in the dropdown considering no spaces are allowed for group names?
Thanks!
4 Replies
Hello,
This is an interesting project, and I would like to split the topic into 2 parts:
- Creating Dynamic Dependent Drop down lists
- Creating a Dynamic Form in Excel
For Creating Dynamic Dependent Drop down lists there are many of my tutorials that tackle this topic, like:
For Creating a Dynamic Form in Excel:
I have a 2 parts tutorial to help you with that:
https://youtu.be/oxHaaOGP15QHope that helps
The issue is that I can only have one group named "Accountability" to reference via indirect Data Validation. I realize that I can technically create variations of the group name "Accountability" for each role type, but I only want the end user to see the "Accountability" in the dropdown menu.
To create a dropdown form in Excel that meets your requirements and behaves as desired, you can employ a combination of named ranges and the INDIRECT function for data validation. The goal is to allow users to select a role and a competency, after which a corresponding set of questions is displayed, without displaying the variations of group names for each role.
Thanks - will give that a try!
