Excel MVP from Brazil Robert Martim shows how to set up a 2-level data validation. Episode 475 shows you how.
Download the file used in this podcast from http://www.mrexcel.com/RM2.zip
Download the file used in this podcast from http://www.mrexcel.com/RM2.zip
Transcript of the video:
Back to the learn Excel podcast.
I'm Bill Jelen.
We're here at the museum of flight in Seattle.
And I've got a lot of Excel MVP's.
Right now, we have Robert Martin.
He's an Excel MVP from Brazil and he has a really cool trick with data validation unlike anything you've ever seen.
All right folks.
I have set up everything because it's a little bit complex.
So what I'm going to do.
I'm going to explain how the formulas work.
Otherwise is going to be very very long.
But you can download these sample and then work through yourself.
So, the idea here is this, we have the continents on Sheet1, along the first line.
And we have countries and we want to validate Column A and Column B.
But Column B is dependent on what you choose.
On color A that is the the continent.
So, let's start by adding a validation.
Using the name continents that I have already created and let's choose here, North America.
So, as you can see we have the three continents which are already here.
Now, let me explain you how this name actually works.
So, going here to define.
We use the function OFFSET to start offsetting on the sheet1,Cell A1.
We have no offsetting here on the line.
We have no offsetting on the column and the height of this offset is equal to one and then we count the number of items are contained in row 1.
Now, the next thing we want to do.
Let me go back here and let to name's to find out on which column of this continent actually is.
Now, if you look down here, you realize that the match function is returning a value based on a relative position.
So if you go back to Sheet2 and type here ncol, which is the name I had created.
It gives us the number of the column.
So, we know that North America is here on column 1 and you could choose say Africa, and it would give you three.
So, that's how the ncall name works.
So now, let's validate here.
Column B, using the name countries that I have already created and as you expect that gives you a list of the countries that you choose.
Now you should notice that it only works If you actually choose a continent.
If you don't, as you can see you can click and nothing happens.
So, let's choose here in North America.
And then we'll choose Canada.
Now, let's see how this name works.
The countries name again.
Use the offset function.
We start the offsetting on Sheet2, on my cell A2.
Now, we don't have any offsetting up on the rows.
But we do have an offsetting of the column.
Now, this upsetting is happening through the name we created for the n call.
We subtract minus 1 because obviously you forget one.
Then we are moving one column to our right, and we don't want to do that.
Now, next thing we have is the count eight function.
Which is going to count the number of items in the respective column.
Then we use the INDIRECT function to return that.
So, we know it's a sheet one.
We use the address function to determine on which column the countries are coming from.
In here, We do a little bit of a trick.
We start on row 1 then we use the Name and call to the side, on which column it is.
We then concatenate again with the address function.
But this time we go all the way up to row 65,000 and we use the N column again to determine on which column we should be counting the number of countries.
Now, this minus one you see here, the reason for that is because as you can see, we have the continent on the first roll.
So, obviously the count A function is going to be counting that one as well.
But we just want to return the country's names.
That's why we have to subtract one.
And finally, the width of this name is obviously equal to 1 because we just want to return the name of the countries according to what I chosen continent.
So, that's how it works.
I hope you enjoyed it.
That's it very good.
Alright! Hey, that was a great tip.
I actually learned that one myself.
I've never..
I've never done it before.
That's Robert Martin from MSOffice gurus.
A Excel MVP from Brazil.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
We're here at the museum of flight in Seattle.
And I've got a lot of Excel MVP's.
Right now, we have Robert Martin.
He's an Excel MVP from Brazil and he has a really cool trick with data validation unlike anything you've ever seen.
All right folks.
I have set up everything because it's a little bit complex.
So what I'm going to do.
I'm going to explain how the formulas work.
Otherwise is going to be very very long.
But you can download these sample and then work through yourself.
So, the idea here is this, we have the continents on Sheet1, along the first line.
And we have countries and we want to validate Column A and Column B.
But Column B is dependent on what you choose.
On color A that is the the continent.
So, let's start by adding a validation.
Using the name continents that I have already created and let's choose here, North America.
So, as you can see we have the three continents which are already here.
Now, let me explain you how this name actually works.
So, going here to define.
We use the function OFFSET to start offsetting on the sheet1,Cell A1.
We have no offsetting here on the line.
We have no offsetting on the column and the height of this offset is equal to one and then we count the number of items are contained in row 1.
Now, the next thing we want to do.
Let me go back here and let to name's to find out on which column of this continent actually is.
Now, if you look down here, you realize that the match function is returning a value based on a relative position.
So if you go back to Sheet2 and type here ncol, which is the name I had created.
It gives us the number of the column.
So, we know that North America is here on column 1 and you could choose say Africa, and it would give you three.
So, that's how the ncall name works.
So now, let's validate here.
Column B, using the name countries that I have already created and as you expect that gives you a list of the countries that you choose.
Now you should notice that it only works If you actually choose a continent.
If you don't, as you can see you can click and nothing happens.
So, let's choose here in North America.
And then we'll choose Canada.
Now, let's see how this name works.
The countries name again.
Use the offset function.
We start the offsetting on Sheet2, on my cell A2.
Now, we don't have any offsetting up on the rows.
But we do have an offsetting of the column.
Now, this upsetting is happening through the name we created for the n call.
We subtract minus 1 because obviously you forget one.
Then we are moving one column to our right, and we don't want to do that.
Now, next thing we have is the count eight function.
Which is going to count the number of items in the respective column.
Then we use the INDIRECT function to return that.
So, we know it's a sheet one.
We use the address function to determine on which column the countries are coming from.
In here, We do a little bit of a trick.
We start on row 1 then we use the Name and call to the side, on which column it is.
We then concatenate again with the address function.
But this time we go all the way up to row 65,000 and we use the N column again to determine on which column we should be counting the number of countries.
Now, this minus one you see here, the reason for that is because as you can see, we have the continent on the first roll.
So, obviously the count A function is going to be counting that one as well.
But we just want to return the country's names.
That's why we have to subtract one.
And finally, the width of this name is obviously equal to 1 because we just want to return the name of the countries according to what I chosen continent.
So, that's how it works.
I hope you enjoyed it.
That's it very good.
Alright! Hey, that was a great tip.
I actually learned that one myself.
I've never..
I've never done it before.
That's Robert Martin from MSOffice gurus.
A Excel MVP from Brazil.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
