You are asking for the opposite of data validation from a list. In effect you want the data validation to fail, instead of succeed, if the value is in the list. This isn't possible with data validation, but a script can do it.
Consider the following script. This script monitors all edits, and pops a message box when a cell value duplicates any other cell value in the same column.
function onEdit( event )
{
// Store the edited sheet.
var sheet_active = event .source .getActiveSheet() ;
// Store the edited range.
var range_active = event .source .getActiveRange() ;
// Store the row, column, and value of the edited cell.
var row_edited = range_active .getRow() ;
var column_edited = range_active .getColumn() ;
var value_edited = range_active .getValue() ;
// Store a range consisting of the column containing the edited cell.
var range_column_edited = sheet_active .getRange(
1 , column_edited ,
sheet_active .getMaxRows() , 1
) ;
// Store an array consisting of the values in the column.
var values_column_edited = range_column_edited .getValues() ;
// Compare each value to the edited cell.
for( var r = 0 ; r < values_column_edited .length ; r++ )
{
if( r+1 == row_edited ) continue ;
if( values_column_edited[r] == value_edited )
Browser .msgBox(
'value_edited="'
+ value_edited
+ '" values_column_edited['
+ r
+ ']="'
+ values_column_edited[r]
+ '"'
) ;
}
}
There will be various practical refinements needed. For example, you might elect to monitor only certain columns, and you might elect to take additional action, such as blanking out the cell value. You may need special handling for blank (missing) values. But this gives you the basic technique that will let you validate.
Update:
To elaborate on the original answer I thought I'd add a few of the validations I personally use that were mentioned in the answer.
// Here's a function I use to assure that only one cell is being edited.
function isRangeSingleCell(range) {
if(range.getRow() === range.getLastRow() && range.getColumn() === range.getLastColumn()) { return true; }
}
To use it just skip the validation if more than one cell is edited
if(!isRangeSingleCell(range_active)) { return; }
You can also skip the validation if the row is not the first row:
if(range_active.getRowIndex() != 1) { return; }
Note: I can't remember off the top of my head if the row counting starts at 0 or 1 so this code may have a bug
The key to onEdit validations is to exit as early as possible to save from unnecessary computation. The quickest exit from a function is an empty return statement.
