Unique entries from a list

[mark hansen]

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

 

[hatman]

Maybe something like this:

Book1
	D	E	F	G	H
4	orange		red	red	yellow
5	red		orange	yellow	green
6	blue		yellow	green	blue
7			green	blue	indigo
8			blue	indigo	violet
9			indigo	violet
10			violet
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]

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]

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]

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.