The source of your data validation list contains blank cells and you don't want those blanks to appear in the dropdown. Bob Umlas combines many advanced Excel techniques to solve this problem. Check out Bob's book at Excel Outside the Box
Transcript of the video:
The MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, episode 1844.
Bob Umlas Validation List No Blanks.
Hey welcome back to the MrExcel netcast.
Today we have a great trick recorded by Bob Umlas, Bob is the author of Excel Outside the Box.
Now this book. It's not your basic Excel book. This is advanced stuff and as you'll see in today's technique.
It combines a lot of different fairly advanced concepts into one to solve a problem and that's what is in Bob's book.
So, today we're taking a look at what he calls data validation on steroids.
Bob. Hi, this is Bob Ulmas from the MVP Summit in Washington.
Talk to you a little bit about Data Validation.
Sometimes you have data that you want to use as validation as a source of a validation, but it has spaces between it and what you do is you set up a Data Validation range that's just that space and if I look at that in fact they do get exactly what you see with all the spaces in it.
What we really want is the same thing without the spaces and this is done, not with any Macros.
May effect.
I'll show you a couple of things if I put in xyz, all right xtz in the middle then it shows up right in here, as well as certainly showing up in here.
So, how this is done is all by formulas which is way off to the side. Let's take a look at first what this Data Validation is and it says =List which doesn't tell us much except I can now go to list and see go to list.
And I can see its way out here, and you can see that clearly is the source of it.
Each one of these is looking at the INDEX of column A for what these numbers are in I to column I.
This one is each one of these is an array formula.
So, the first one is an array formula filled down and once I explain this you'll see how this whole thing works.
The NUM errors are not a problem. Matter of fact it's kind of necessary.
So I'm going from A1 to A22 I can go further currently A22 is the last one that had data in it has the word validation.
So, it's saying if A1 to A22 = blank or nothing, then show nothing.
Otherwise, just give me the row so, this part of it. Let me grab that parentheses. If I hit the F9 key to evaluate that, then I get wherever there is a value I get a number.
If there's no value I get a blank.
Notice that these numbers that you see in the formula bar are the same numbers that are showing up here.
So, I'm taking the smallest one of these. I want, the first one I want is the smallest number which is A2.
The second one I want is the second smallest number which is A6 and so on.
So, I'm using the row of A1 because as I fill that down it becomes the row of A2 and the row of A3 ofcourse which is row of A1 is 1, row of A2 is 2 and I get the first second and third smallest.
This one gives me an error because there is no ninth smallest.
There's only eight items in there.
So, here's the list of all the items that are put in there and if I put in the xtz again, you'll see all this push down and might as well do that. So I put an xtz in here and when I branch off to the right you'll see that the xtz fit into place, and there's only two of these errors, #NUM! errors.
I have to define a name, it was called list and I have to know how far down to go, and that's taken cell K1 and this is saying find that error match true where there's an error in column J and it's finding it now in the 10th position.
So, what's the definition of list if I go to Formulas and Name Manager, and I look at List you can see that's an OFFSET of and this is just called Sheet 1 so the OFFSET of J1 which is the word here 0 Columns, 0 rows down, 0 columns over and then I'm using that item in K1 which is the number 10 minus 1 because the number 10 is the error the NUM value.
So, came on minus 1 is 9 and I want one column.
So, I'm taking the first nine items in here using that as the list and that shows up in the Data Validation.
Cool, right, I think so too. Thanks.
Yes, Bob. We do think that's cool and you should come out here to the website mrexcel.com/outsidethebox.html ebook version just 1495 you can download it and just learn all kinds of great techniques.
Like that one and many many more.
Hey. I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast, episode 1844.
Bob Umlas Validation List No Blanks.
Hey welcome back to the MrExcel netcast.
Today we have a great trick recorded by Bob Umlas, Bob is the author of Excel Outside the Box.
Now this book. It's not your basic Excel book. This is advanced stuff and as you'll see in today's technique.
It combines a lot of different fairly advanced concepts into one to solve a problem and that's what is in Bob's book.
So, today we're taking a look at what he calls data validation on steroids.
Bob. Hi, this is Bob Ulmas from the MVP Summit in Washington.
Talk to you a little bit about Data Validation.
Sometimes you have data that you want to use as validation as a source of a validation, but it has spaces between it and what you do is you set up a Data Validation range that's just that space and if I look at that in fact they do get exactly what you see with all the spaces in it.
What we really want is the same thing without the spaces and this is done, not with any Macros.
May effect.
I'll show you a couple of things if I put in xyz, all right xtz in the middle then it shows up right in here, as well as certainly showing up in here.
So, how this is done is all by formulas which is way off to the side. Let's take a look at first what this Data Validation is and it says =List which doesn't tell us much except I can now go to list and see go to list.
And I can see its way out here, and you can see that clearly is the source of it.
Each one of these is looking at the INDEX of column A for what these numbers are in I to column I.
This one is each one of these is an array formula.
So, the first one is an array formula filled down and once I explain this you'll see how this whole thing works.
The NUM errors are not a problem. Matter of fact it's kind of necessary.
So I'm going from A1 to A22 I can go further currently A22 is the last one that had data in it has the word validation.
So, it's saying if A1 to A22 = blank or nothing, then show nothing.
Otherwise, just give me the row so, this part of it. Let me grab that parentheses. If I hit the F9 key to evaluate that, then I get wherever there is a value I get a number.
If there's no value I get a blank.
Notice that these numbers that you see in the formula bar are the same numbers that are showing up here.
So, I'm taking the smallest one of these. I want, the first one I want is the smallest number which is A2.
The second one I want is the second smallest number which is A6 and so on.
So, I'm using the row of A1 because as I fill that down it becomes the row of A2 and the row of A3 ofcourse which is row of A1 is 1, row of A2 is 2 and I get the first second and third smallest.
This one gives me an error because there is no ninth smallest.
There's only eight items in there.
So, here's the list of all the items that are put in there and if I put in the xtz again, you'll see all this push down and might as well do that. So I put an xtz in here and when I branch off to the right you'll see that the xtz fit into place, and there's only two of these errors, #NUM! errors.
I have to define a name, it was called list and I have to know how far down to go, and that's taken cell K1 and this is saying find that error match true where there's an error in column J and it's finding it now in the 10th position.
So, what's the definition of list if I go to Formulas and Name Manager, and I look at List you can see that's an OFFSET of and this is just called Sheet 1 so the OFFSET of J1 which is the word here 0 Columns, 0 rows down, 0 columns over and then I'm using that item in K1 which is the number 10 minus 1 because the number 10 is the error the NUM value.
So, came on minus 1 is 9 and I want one column.
So, I'm taking the first nine items in here using that as the list and that shows up in the Data Validation.
Cool, right, I think so too. Thanks.
Yes, Bob. We do think that's cool and you should come out here to the website mrexcel.com/outsidethebox.html ebook version just 1495 you can download it and just learn all kinds of great techniques.
Like that one and many many more.
Hey. I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
