2 Dynamic Dropdowns
Task explanation.
Imagine you are doing a study on peoples heights and you are trying to find out if the country and province you are from could have an effect on it.
You track your data in excel and have built some sweet dropdown menus but after you choose the country you get the states from all the countries and it would be ideal if you only got the states from the country you picked.
If you try Cell B6, C6 and D6 you will notice the dropdowns but the C6 one is very long and it would be ideal if the dropdown was shorter depending on the country you choose.
Could you figure this out?
🌶 Spicy Challenge. 🌶
How to details.
Alright, So this exercise lies in the understanding of 2 functions.
Indirect which will help you build the dropdown that feeds from the last list and the ability to name cells
The key is to create 3 Names - One for each country - And these names need to be the exact same as the country name.
To do so, go in the LIST sheet and select all the states for USA and name these values USA. To name it, go in top left name box and type the name and hit ENTER. Repeat this process for each series of states/provinces.
Next once you have repeated the step of naming each series of states. USA, Canada, Mexico you then go back in the ENTRIES sheet and select the entire collumn and hit ALT-A-V-V or go to the Data Tab and select Data Validation. Select list from the Allow field and type a very simple function:
=INDIRECT(B1)
Now after you've done this, it's functionnal! Give it a go!
