Today's episode is a follow-up from Episode #1583.
After following the instructions from that earlier Episode, a viewer could not get the Validation setup to work for him. Today in Episode #1606, Bill re-addresses the Validation setup to tweak it for this viewer to use.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
After following the instructions from that earlier Episode, a viewer could not get the Validation setup to work for him. Today in Episode #1606, Bill re-addresses the Validation setup to tweak it for this viewer to use.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
MrExcel podcast; Really Dependent Validation.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
While this cuz all the way back to Episode 383.
I just got a comment from somebody said "Hey I'm completely new to Excel, I'm trying to follow your episode 383 and --it's not working for me and what he had, he had a form that people were trying to fill out here with data validation and he had successfully set up this drop down, to come from this list back here in the second sheet.
So, I'm looking at two different sheets in the same workbook here.
That's View New Window and then arrange all vertical.
All right so, he had...
Well let's just do it Data Validation and set up to refer to a range called Customer.
We'll take a look at that.
But once he chose a Customer from the list, see some of these customers are in here twice with two different addresses.
So, when he chooses a Customer from the list, he wants to be able to choose an address from the list.
That you know, is just the addresses for the Cozy Corner let's say.
Off course, these are all made up addresses for Cozy Corner really not here.
So, you know then we get down to the Address Validation and he was trying to follow the podcast 383 to do that but this is a lot harder than what we have to do in podcast 383.
Let's take a look at the actual solution here.
I said what you have to do, is you're going to use the offset function and that's one of the hardest functions in Excel.
I apologized, I said I'm sorry that sometimes things in Excel are really really hard to do.
How we're going to start from the customer list sheet, cell B1, that's the start from and then they want to know how many rows down, do we go, to get to the start of this range.
so that's the Match of what they entered in cell B2, in this list of Customers over here.
We want an exact match so that will say, hey Cozy Corner is down 1,2,3,4 rows from that spot.
How many rows over, well now, I don't want to go any rows over from B1.
I want to stay right there in column B and then the height, I did a Count If.
Look through the customers, see how many are equal to Cozy Corner, that should be 2 and I warned him, I said 'you're gonna have to make sure that this data is sorted and that all of the like customers are together' and then the width is 1, so I took that whole formula and I went off to Formulas and I did Define Name, since it's already defined.
We're gonna have to just edit here and so I created a name called Addresses and paste that big huge formula in there and you know I always get this wrong every single time.
You have to be really careful to put those dollar signs around B1.
Everything has to have dollar signs in this box, otherwise it gets completely screwed up.
So, click OK, click Close.
Let's go back and test it out.
So, we choose Cozy Corner, we should be offered 1621 Tropical Trail or 421 Courtenay.
Beautiful that works, we come up here and we'll choose Serving Brevard Reality and we should be offered 1921 and 921.
Beautiful that's working, isn't that cool?
and they said okay, now I need a way for the Customer list to automatically grow, as extended and that's actually simpler, than what we just did.
Let's take a look at the formula for that, we just start from customer list A1.
How many rows down: 1 row down.
How many columns over: 0 and then what's the Height?
All we just do a count A of column A, minus 1, with a Width of 1.
So, we'll take a look at that and then Name Manager, edit and you're good to go.
All right now, so I've been having this conversation on YouTube and I said you know the real issue that I think would drive me crazy is the fact that the various items are in the list, multiple times.
Wouldn't you wanna get down to the point where you had Kings Duck Inn, just in there twice instead of, just in there once, instead of two times.
So I suggested taking this list, your customer list, coming at least a few columns away, Pasting and doing Data, Remove Duplicates, click OK.
Now you just have a unique list of Customers and then redefine the Customer list to start in column E.
So right, here we start in E instead of A and we're going to count the number of Customers in E, instead of A, click OK, click Close.
Now, I think we've got it, we have Truly Dependent Validation.
Everyone is appearing just once and once you choose the Customer, you get just the possible Addresses in the whole list.
So, nice improvement on podcast 383; Truly Dependent Validation.
All right hey, I wanna thank you for stopping by. We'll see you next time for another netcast from MrExcel.
MrExcel podcast; Really Dependent Validation.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
While this cuz all the way back to Episode 383.
I just got a comment from somebody said "Hey I'm completely new to Excel, I'm trying to follow your episode 383 and --it's not working for me and what he had, he had a form that people were trying to fill out here with data validation and he had successfully set up this drop down, to come from this list back here in the second sheet.
So, I'm looking at two different sheets in the same workbook here.
That's View New Window and then arrange all vertical.
All right so, he had...
Well let's just do it Data Validation and set up to refer to a range called Customer.
We'll take a look at that.
But once he chose a Customer from the list, see some of these customers are in here twice with two different addresses.
So, when he chooses a Customer from the list, he wants to be able to choose an address from the list.
That you know, is just the addresses for the Cozy Corner let's say.
Off course, these are all made up addresses for Cozy Corner really not here.
So, you know then we get down to the Address Validation and he was trying to follow the podcast 383 to do that but this is a lot harder than what we have to do in podcast 383.
Let's take a look at the actual solution here.
I said what you have to do, is you're going to use the offset function and that's one of the hardest functions in Excel.
I apologized, I said I'm sorry that sometimes things in Excel are really really hard to do.
How we're going to start from the customer list sheet, cell B1, that's the start from and then they want to know how many rows down, do we go, to get to the start of this range.
so that's the Match of what they entered in cell B2, in this list of Customers over here.
We want an exact match so that will say, hey Cozy Corner is down 1,2,3,4 rows from that spot.
How many rows over, well now, I don't want to go any rows over from B1.
I want to stay right there in column B and then the height, I did a Count If.
Look through the customers, see how many are equal to Cozy Corner, that should be 2 and I warned him, I said 'you're gonna have to make sure that this data is sorted and that all of the like customers are together' and then the width is 1, so I took that whole formula and I went off to Formulas and I did Define Name, since it's already defined.
We're gonna have to just edit here and so I created a name called Addresses and paste that big huge formula in there and you know I always get this wrong every single time.
You have to be really careful to put those dollar signs around B1.
Everything has to have dollar signs in this box, otherwise it gets completely screwed up.
So, click OK, click Close.
Let's go back and test it out.
So, we choose Cozy Corner, we should be offered 1621 Tropical Trail or 421 Courtenay.
Beautiful that works, we come up here and we'll choose Serving Brevard Reality and we should be offered 1921 and 921.
Beautiful that's working, isn't that cool?
and they said okay, now I need a way for the Customer list to automatically grow, as extended and that's actually simpler, than what we just did.
Let's take a look at the formula for that, we just start from customer list A1.
How many rows down: 1 row down.
How many columns over: 0 and then what's the Height?
All we just do a count A of column A, minus 1, with a Width of 1.
So, we'll take a look at that and then Name Manager, edit and you're good to go.
All right now, so I've been having this conversation on YouTube and I said you know the real issue that I think would drive me crazy is the fact that the various items are in the list, multiple times.
Wouldn't you wanna get down to the point where you had Kings Duck Inn, just in there twice instead of, just in there once, instead of two times.
So I suggested taking this list, your customer list, coming at least a few columns away, Pasting and doing Data, Remove Duplicates, click OK.
Now you just have a unique list of Customers and then redefine the Customer list to start in column E.
So right, here we start in E instead of A and we're going to count the number of Customers in E, instead of A, click OK, click Close.
Now, I think we've got it, we have Truly Dependent Validation.
Everyone is appearing just once and once you choose the Customer, you get just the possible Addresses in the whole list.
So, nice improvement on podcast 383; Truly Dependent Validation.
All right hey, I wanna thank you for stopping by. We'll see you next time for another netcast from MrExcel.
