Ian wrote in looking for an array formula that would build a unique list of managers who belong to a selected VP. Since Ian has Excel 2013, I propose using Slicers instead of the insanely difficult array formulas.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1798: Three-Level Dependent Validation Using Slicers.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Ian.
Ian has a database.
I’m sure his database is much larger than this and he has data validation, set up here to choose a VP.
Once we choose the VP, he then wants a second data validation that will give him just the managers assigned to that particular VP and well, this could be done with a formula from Mike Kerven’s book you know we then have to make a second right for me to get just the unique values and then feed that into data validation.
I said, wait a second, wait a second if we're using Excel 2013 there's a much easier way to go so let's just get rid of all of these crazy formulas out here and select the original data make it into a table with ctrl+T click OK and then starting in Excel 2013.
Now, we can insert a slicer so insert a slicer for VP and manager and maybe even tech I don't know.
Click OK and didn't ask for that, but you could imagine where that would come up next.
So, we have a drop down here where we choose the VP and then another drop-down where we choose the managers not really a drop-down it's a slicer and then choose the tech.
The beautiful thing about slicers and we'll go with two columns here.
The beautiful thing about slicers is once you select from one slicer all the other slicers react to that.
So, if we would choose Jenny then the managers that are not valid for Jenna get grayed out and so it's obvious here you know which which managers are still in play and which texts are still in play.
So, change the colors just a simple will choose Vickie choose manager II and then those are the two choices of very fast and intuitive way to filter this data using in essence, what we would do is a three level dependent validation but without having to do all of the hard hard array formulas.
Hey, I want to thank Ian for sending that question and I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1798: Three-Level Dependent Validation Using Slicers.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Ian.
Ian has a database.
I’m sure his database is much larger than this and he has data validation, set up here to choose a VP.
Once we choose the VP, he then wants a second data validation that will give him just the managers assigned to that particular VP and well, this could be done with a formula from Mike Kerven’s book you know we then have to make a second right for me to get just the unique values and then feed that into data validation.
I said, wait a second, wait a second if we're using Excel 2013 there's a much easier way to go so let's just get rid of all of these crazy formulas out here and select the original data make it into a table with ctrl+T click OK and then starting in Excel 2013.
Now, we can insert a slicer so insert a slicer for VP and manager and maybe even tech I don't know.
Click OK and didn't ask for that, but you could imagine where that would come up next.
So, we have a drop down here where we choose the VP and then another drop-down where we choose the managers not really a drop-down it's a slicer and then choose the tech.
The beautiful thing about slicers and we'll go with two columns here.
The beautiful thing about slicers is once you select from one slicer all the other slicers react to that.
So, if we would choose Jenny then the managers that are not valid for Jenna get grayed out and so it's obvious here you know which which managers are still in play and which texts are still in play.
So, change the colors just a simple will choose Vickie choose manager II and then those are the two choices of very fast and intuitive way to filter this data using in essence, what we would do is a three level dependent validation but without having to do all of the hard hard array formulas.
Hey, I want to thank Ian for sending that question and I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
