Unique entries from a list

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
523
Office Version
  1. 2016
I thought I saw this up here somewhere but can't find it.

I'm looking for a way to stop a user from selecting an item from a drop down box, if that item has been selected before.

Example

I have a group of cells A10:A15. Each one of those cell has data validate to have a drop down box from my list.

then the user selects and item from the list... No problem.
If they move down and select an different item ... No problem.
BUT if in the third box, they then try to select the same item in either of the two boxes, they are not allowed. Warning message and all that.

I saw something like this up here before and I normally try to save things like that but I guess I let that one slip by. Its wasn't with macros it was something else.

Thanks for any help.
Mark
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Maybe something like this:
Book1
DEFGH
4orangeredredyellow
5redorangeyellowgreen
6blueyellowgreenblue
7greenblueindigo
8blueindigoviolet
9indigoviolet
10violet
Sheet1


Where the validation list for D4 is F4:F10, D5 is G4:G9, D6 is H4:H8... this can be cascaded as far as you like... then use conditional formatting to verify that the current selection for each validation cell is contained in the validation list.
 

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
523
Office Version
  1. 2016
Thanks for the quick reply. That's not what I remember it was done before...

I was thinking it was something like a data validate (if so, I'm sunk because I'm already doing that for these cells to get the list) or maybe is was another way I can't remember.

Either way, It's a neat trick I could use for other things.

Thanks again.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Mark

A solution is:

- Build an auxilliary list with the options not yet chosen.

- Base your data validation in that list.

The validation list is the same for all cells, forcing no duplicate choices.

You can see the mechanics of the solution here:

http://www.contextures.com/xlDataVal03.html

Hope this helps
PGC
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
There are a variety of ways of preventing the user from selecting something that is already selected... or warning them not to. And they have all been posted on this board, at one time or another. As you state: using Data Validate in this case is not an option because you are already using it... hence the system I propose. You could as easily use Conditonal Formatting to change the color of the cells if a list member has already been chosen by the user.
 

Forum statistics

Threads
1,136,613
Messages
5,676,813
Members
419,652
Latest member
jjakub33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top