Can you set up Excel Data Validation so that the choices in a second drop-down menu are dependent on the choice in the first menu? For example, if you choose Bagels, the answers might be Raisin, Tomato, or Everything. If you choose Pancakes, then the answers would be Buckwheat, Blueberry, or Pumpkin.
This has been solved twice before on my channel using Names or using OFFSET. Today, with the introduction of new Dynamic Array formulas, there could be an easier way.
This has been solved twice before on my channel using Names or using OFFSET. Today, with the introduction of new Dynamic Array formulas, there could be an easier way.
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2248: Dependent Validation Using Arrays.
Well, hey.
This has been addressed twice before on the podcast, how to do dependent validation, and what dependent validation is is you get to choose, first, a category and then, in response, to that, the second drop-down will change to just the items from that category, and, before, this was complicated, and with the new dynamic arrays that were announced in September of 2018…and these are rolling out, so you have to have Office 365.
Right now October 10th, I've heard that they are on about 50% of the Office insiders, so they're rolling them out very slowly.
It'll probably be through the first half of 2019 before you get these, but it will allow us to do dependent validation in a much easier fashion.
So, I have two formulas here.
The first formula is the UNIQUE of all of the classifications and I sent that into the SORT command.
So, that gives me 1 formula returning 5 results and that lives in D4.
So, here, where I want to choose the data validation, I'll [ DL – 1:09 ]…the SOURCE is going to be =D4#.
That # -- we've been calling it the spiller -- make sure that it returns all of the results from D4.
So, if I would add a new category over here and this grows, D4# will pick up that extra amount, alright?
[ =SORT(UNIQUE(B4:B23)) ] So, that first validation is fairly simple, but now that we know that we've chosen CITRUS -- this is going to be more difficult -- I want to filter the list in column A where the item in column B equals the chosen item, alright?
So, first we have to let them choose something and then, once I know it's CITRUS, then give me the LIME, ORANGE, and TANGERINE, they would choose something else.
BERRY.
Check this out.
The scientific journals say that a banana is a berry.
I don't agree with that.
Doesn’t feel like a berry to me but don't blame me.
I'm just, you know, using the Internet.
BANANA, ELDERBERRY, and RASPBERRY.
Now, you know, the hassle with this is someone's going to initially come here without having chosen anything, and, so in that case, we have CHOOSE CLASS FIRST which is that third argument that says if nothing is found, alright?
So, you know, that way, if we start out in this scenario, the choice is going to be CHOOSE CLASS FIRST.
The idea is they choose the CLASS, VEGETABLE, this updates, and then those items come from that list.
The DATA VALIDATION here, of course, well, that's another spiller, =E4# to get that to work, alright?
So, this is cool.
(=FILTER(A4:A23,B4:B23=H3,”Choose Class First”) ] Check out my book Excel Dynamic Arrays.
This is…it's going to be free through the end of 2018.
Check the link down there in the YouTube description, how you can download it, for this very example plus 29 other examples of how to use these items.
Well, wrap up for today.
Dynamic arrays give us another way to do dependent validation.
If you're not on Office 365 and you don't have these yet, feel free to go back to, I suppose, video 1606 that shows the old way to do this.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, hey.
This has been addressed twice before on the podcast, how to do dependent validation, and what dependent validation is is you get to choose, first, a category and then, in response, to that, the second drop-down will change to just the items from that category, and, before, this was complicated, and with the new dynamic arrays that were announced in September of 2018…and these are rolling out, so you have to have Office 365.
Right now October 10th, I've heard that they are on about 50% of the Office insiders, so they're rolling them out very slowly.
It'll probably be through the first half of 2019 before you get these, but it will allow us to do dependent validation in a much easier fashion.
So, I have two formulas here.
The first formula is the UNIQUE of all of the classifications and I sent that into the SORT command.
So, that gives me 1 formula returning 5 results and that lives in D4.
So, here, where I want to choose the data validation, I'll [ DL – 1:09 ]…the SOURCE is going to be =D4#.
That # -- we've been calling it the spiller -- make sure that it returns all of the results from D4.
So, if I would add a new category over here and this grows, D4# will pick up that extra amount, alright?
[ =SORT(UNIQUE(B4:B23)) ] So, that first validation is fairly simple, but now that we know that we've chosen CITRUS -- this is going to be more difficult -- I want to filter the list in column A where the item in column B equals the chosen item, alright?
So, first we have to let them choose something and then, once I know it's CITRUS, then give me the LIME, ORANGE, and TANGERINE, they would choose something else.
BERRY.
Check this out.
The scientific journals say that a banana is a berry.
I don't agree with that.
Doesn’t feel like a berry to me but don't blame me.
I'm just, you know, using the Internet.
BANANA, ELDERBERRY, and RASPBERRY.
Now, you know, the hassle with this is someone's going to initially come here without having chosen anything, and, so in that case, we have CHOOSE CLASS FIRST which is that third argument that says if nothing is found, alright?
So, you know, that way, if we start out in this scenario, the choice is going to be CHOOSE CLASS FIRST.
The idea is they choose the CLASS, VEGETABLE, this updates, and then those items come from that list.
The DATA VALIDATION here, of course, well, that's another spiller, =E4# to get that to work, alright?
So, this is cool.
(=FILTER(A4:A23,B4:B23=H3,”Choose Class First”) ] Check out my book Excel Dynamic Arrays.
This is…it's going to be free through the end of 2018.
Check the link down there in the YouTube description, how you can download it, for this very example plus 29 other examples of how to use these items.
Well, wrap up for today.
Dynamic arrays give us another way to do dependent validation.
If you're not on Office 365 and you don't have these yet, feel free to go back to, I suppose, video 1606 that shows the old way to do this.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
