Validation list that removes chosen values

[gregory.blanch]

I am trying to create a validation list using a name range. The idea
is to only allow each of the values in the range to only be chosen
once. For example this validation would be applied to cells A1:A10.
There would be 10 values in the named range, and each time one of the
values was chosen it would not be available in the other cells - each
cell would have a mutually exclusive value.

Any help would be much appreciated.

 

[Tyro]

Select data validation, Settings, Allow, List. Then select the range that
has your list of allowed values

Tyro

 

[T. Valko]

See this:

http://contextures.com/xlDataVal03.html

 

[gregory.blanch]

Select data validation, Settings, Allow, List. Then select the range that
has your list of allowed values







- Show quoted text -

Sorry, I was not clear about my request
These are the steps

1. Create a named range on sheet 1 called fruit. The range is defined
as Z1:Z10. The cells Z1 through Z10 have values such as apple, orange,
peach. pear, mandarin, grape etc
2. Apply validation to cells A1:A10 using the list function. The
source is defined as =fruit

The user can now select Apple in A1 through a drop down list. They can
then move to A2 and select Apple again.

What I want to be able to do is once Apple is chosen in A1, the values
available in the drop down for A2 don't include Apple, and so on. So
in the end each of the cells in A1:A10 must have an unique value.

Hope this is a bit clearer

 

[gregory.blanch]

See this:

http://contextures.com/xlDataVal03.html

--
Biff
Microsoft Excel MVP







- Show quoted text -

Brilliant BIF - thats exactly what I want. Thanks