Data Validation by Exclusion (Excel 2000)

[TQ2]

In using data validation, one can have a list. Only data from that list can be input. Is it possible to reserve this: if a value (a string, a word) is in the list, it cannot be input into a cell.

[kieran]

It can be done with something like
=ISNA(VLOOKUP(D2, A2:A5,1,0)) where the validated (input) cell is D2 and the lsit of values to exclude is in A2:A5.

Use data validation, custom and enter the formula as shown with the relevant range for your data.


Hope it helps

[sdckapr]

I don't think you can do it with a list, if you list of "can Not use" is in the range c1:c10 (change as appropriate) you can choose custom - formula
=ISNA(MATCH(A1,C1:C10,FALSE))

and you will not be able to enter anything from this list. You probably should add the list of items in the "error box" to let people know what not to enter.

Steve