Data Validation

[akbar]

Hello,

I have a data validation list on cell A2:A11,what i want is not to display the duplicate values (such as apple & oranges
twice) in the list without changing the values in the validation table..is it possible..second thing is when i check apple
on A2 only chile and Poland should appear on B2 and the blank when i open the list should be avoided without changing the validation
list..third-thing is to get the ref# no on c3 on condition if apple matches chile then Ref should be 000011..

Please check the attached file for further ref

Thanks In Advance,
Ak

[Ace_XL]

You could use defined name ranges for each fruit to pick only relevant countries in the drop-down list. A simple Vlookup after that can pick the code.

See attached. Is this something you can work with?

This thread too might prove useful in case you do not want to make separate named ranges for each fruit.

http://www.excelforum.com/excel-gene...l-entries.html

[akbar]

Thanks Ace it works..but i want to know whether i can ignore the duplicate values and blank cells in the list or not..and the link
you have shown is a good example how to do that but i didn't get how you did it..the validation list in f3 shows only =a..

Thanks,
Ak

[Ace_XL]

Yes you could ignore the duplicate and blank values.

The =a (in the other thread) is a defined name range called 'a' which uses the 'offset' & 'match' function to determine the drop down list. Have a look in the name manager.

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$F$2,Sheet1!$B$1:$D$1,0),COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH(Sheet1!$F$2,Sheet1!$B$1:$D$1)))-1,1)

[akbar]

Thanks Ace it works..

Thanks,
Ak

[akbar]

Ace validation seems not to ignore duplicate values..how is it possible..

Thanks,
Ak

[Ace_XL]

You do not need the duplicate values in your list at all. You can simply delete them and have a single list for the fruits.

With the defined name ranges in place for each fruit, the second drop-down validation (for countries) will pick only relevant lists and no duplication of fruit names is required.

Does that make sense?

[akbar]

Thanks Ace...