Greg wants to set up dynamic validation, but he has a wrinkle....Once someone chooses from the first data set, he needs to append the word My before that answer to get to the name of the second data set. Episode 1130 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Wow, a great question sent in by Greg today, I had to go check this one out before I figured out if I could do it or not.
Greg has a couple of range names here, something called MyDogs and something called MyCats, and then we're going to set up a simple data validation here, so Alt D L. We’re going to allow a List, and the list is going to be this range here where they can choose Dogs or Cats, click OK, alright!
So now that works alright, you can choose Dogs or Cats.
Now here's Greg's problem, Greg says then “Once they've chosen that, I want to have a second list that gives them either Collie Poodle Spaniel, or Tabby Siamese Hairless, depending on what they chose.
And the hassle is that these names don't match exactly, see, it has the word “My” before it.” Now.
I could argue with Greg “Hey, just take the word ‘My’ out!” But let's say that we can't, so our second choice here is Data Validation.
We're going to allow a List, and usually what we put in here is a formula that is the indirect of G1, but it works if we do =INDIRECT of the word “My”&G1.
So, what's that going to do, that's going to take the word “My” and put it before Dogs, so we choose Dogs here, we should get a list of dogs from A1.
If we choose Cats here we should get Tabby Siamese Hairless, let’s try it!
Alright, so we choose Dogs, come down here, and it gives me the list of dogs.
If we instead would choose Cats and then come here, check it out, the list has changed dynamically to go grab the MyCats range.
Very, very cool tricks.
I want to thank Greg for sending that great question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Wow, a great question sent in by Greg today, I had to go check this one out before I figured out if I could do it or not.
Greg has a couple of range names here, something called MyDogs and something called MyCats, and then we're going to set up a simple data validation here, so Alt D L. We’re going to allow a List, and the list is going to be this range here where they can choose Dogs or Cats, click OK, alright!
So now that works alright, you can choose Dogs or Cats.
Now here's Greg's problem, Greg says then “Once they've chosen that, I want to have a second list that gives them either Collie Poodle Spaniel, or Tabby Siamese Hairless, depending on what they chose.
And the hassle is that these names don't match exactly, see, it has the word “My” before it.” Now.
I could argue with Greg “Hey, just take the word ‘My’ out!” But let's say that we can't, so our second choice here is Data Validation.
We're going to allow a List, and usually what we put in here is a formula that is the indirect of G1, but it works if we do =INDIRECT of the word “My”&G1.
So, what's that going to do, that's going to take the word “My” and put it before Dogs, so we choose Dogs here, we should get a list of dogs from A1.
If we choose Cats here we should get Tabby Siamese Hairless, let’s try it!
Alright, so we choose Dogs, come down here, and it gives me the list of dogs.
If we instead would choose Cats and then come here, check it out, the list has changed dynamically to go grab the MyCats range.
Very, very cool tricks.
I want to thank Greg for sending that great question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
