Ben asks todays question: Can I set up Validation Dropdowns where the list of items requires a unique entry? For example, once I choose ABC in a column, it should no longer be offered in that column. Episode 637 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Wow! We have a incredibly tough one today.
This question was sent in by Ben.
My first read, Ben's question is like, there's no way to do this.
But then I started a puzzle through it And it turns out that there Ben wants to use data validation And he wants to make sure that once an item has been selected from the list, it's no longer available to be entered anywhere else in the list.
So, we have basically set up a spreadsheet here and let's say that we're going to enter values in this green area.
Enter values in this green area and then I said okay.
Let's come over to column E.
Type a list of all the possible values.
All the possible values.
So, they're here in this list and basically once the line has been selected from the list, we don't want it to appear in the data validation anymore.
So, I came over to column F and created a column that says, has the value been used.
Basically, this is a COUNTIF function that goes and checks to see if the value CA is in that green range.
If it is then it says give me a minus 999, otherwise give me the largest value so far in this column plus 1 And basically, that forces Excel to give us the numbers one, two, three, four, five, six, seven.
But as soon as we choose a value, let me put, Arizona.
You'll see that Arizona gets a minus 999 and Florida now becomes 2.
Illinois becomes 3 and so on down the line.
So, what we did then over here in column I, is basically almost like a v lookup.
I couldn't do a v lookup because the numbers would have to be to the left of the column and so I used basically an index and match.
Index and match says hey go find the value that's assigned to number one and then number two and you see that here Florida is the Second value.
It's ignoring Arizona because it has the minus 999.
So, we have this formula.
That works pretty well.
Except for we get a few N/A' s down at the bottom of the list.
As I enter more values back in the input area, more N/A's are going to float down to the bottom of list.
So, in my dynamic range name, I need to have a count of all the N/A's.
So, I use a ARRAY formula over here.
That says equal if Is Na I2 to I52 then give me a 0 otherwise, give me a 1.
[ CTRL+SHIFT+ENTER ] to enter that and it tells me how many values we have.
Okay! We're almost there.
Finally we go to insert name to find.
And we use the trick from one of the previous podcasts, where we say my list instead of just being this unique list from I2, starts at I2 go 0 rows down, 0 rows over How many rows tall is that.
Well, we use the result of our array formula over here.
So in this case, it's going to be 49 and one column wide.
Now, back in our original data set.
We set up our data validation to use that name.
So, data validation allow a list, equal to my list.
Click OK.
In the way that it works now.
When we go to the second value, Arizona has already been chosen.
So, it's not on the list.
We'll choose something else.
I'll choose Ohio.
And basically now, Ohio Gets a negative 999, is to remove from our list.
And when we go to the third value, you'll see that Ohio is no longer in the list.
We only have the remaining items in the list, and it works all the way down.
So,When Ben sent me this question, I was like Ben there's no way to do this.
Well it turns out there is a way.
It's beyond What most people would ever consider doing.
But if you desperately need a way to do this, it certainly would work.
So, thanks to Ben for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Wow! We have a incredibly tough one today.
This question was sent in by Ben.
My first read, Ben's question is like, there's no way to do this.
But then I started a puzzle through it And it turns out that there Ben wants to use data validation And he wants to make sure that once an item has been selected from the list, it's no longer available to be entered anywhere else in the list.
So, we have basically set up a spreadsheet here and let's say that we're going to enter values in this green area.
Enter values in this green area and then I said okay.
Let's come over to column E.
Type a list of all the possible values.
All the possible values.
So, they're here in this list and basically once the line has been selected from the list, we don't want it to appear in the data validation anymore.
So, I came over to column F and created a column that says, has the value been used.
Basically, this is a COUNTIF function that goes and checks to see if the value CA is in that green range.
If it is then it says give me a minus 999, otherwise give me the largest value so far in this column plus 1 And basically, that forces Excel to give us the numbers one, two, three, four, five, six, seven.
But as soon as we choose a value, let me put, Arizona.
You'll see that Arizona gets a minus 999 and Florida now becomes 2.
Illinois becomes 3 and so on down the line.
So, what we did then over here in column I, is basically almost like a v lookup.
I couldn't do a v lookup because the numbers would have to be to the left of the column and so I used basically an index and match.
Index and match says hey go find the value that's assigned to number one and then number two and you see that here Florida is the Second value.
It's ignoring Arizona because it has the minus 999.
So, we have this formula.
That works pretty well.
Except for we get a few N/A' s down at the bottom of the list.
As I enter more values back in the input area, more N/A's are going to float down to the bottom of list.
So, in my dynamic range name, I need to have a count of all the N/A's.
So, I use a ARRAY formula over here.
That says equal if Is Na I2 to I52 then give me a 0 otherwise, give me a 1.
[ CTRL+SHIFT+ENTER ] to enter that and it tells me how many values we have.
Okay! We're almost there.
Finally we go to insert name to find.
And we use the trick from one of the previous podcasts, where we say my list instead of just being this unique list from I2, starts at I2 go 0 rows down, 0 rows over How many rows tall is that.
Well, we use the result of our array formula over here.
So in this case, it's going to be 49 and one column wide.
Now, back in our original data set.
We set up our data validation to use that name.
So, data validation allow a list, equal to my list.
Click OK.
In the way that it works now.
When we go to the second value, Arizona has already been chosen.
So, it's not on the list.
We'll choose something else.
I'll choose Ohio.
And basically now, Ohio Gets a negative 999, is to remove from our list.
And when we go to the third value, you'll see that Ohio is no longer in the list.
We only have the remaining items in the list, and it works all the way down.
So,When Ben sent me this question, I was like Ben there's no way to do this.
Well it turns out there is a way.
It's beyond What most people would ever consider doing.
But if you desperately need a way to do this, it certainly would work.
So, thanks to Ben for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
