Data validation - allow only certain numbers 2

[jh3016]

I have an Excel spreadsheet that has a column with numbers in it. In the column, I want to have a data validation so that if any numbers that exist on a list (that I will have on a separate worksheet) are attempted to be entered into the spreadsheet, I want a message to appear "this number has been used before".

Is there a way to do this?

Thanks in advance.

 

[ETID]

Highlite your predefined list and give it a range name


Highlite the input column in question...then from menu

Goto Data>Validation....

select custom

then =your_range_name in the formula bar

 

[mbarron]

Yes there is.

Set the Criterea to Custom.
In the formula use:
=IF(ISNA(VLOOKUP(cell,values,1,0)),TRUE,FALSE)

Where cell is your cell you are validating, and value is the values to be checked.

Mike

 

[jh3016]

Thanks for the quick response. I'm sure both would have worked, but I used mbarron's response. It worked like a charm.

Thanks.

 

[ETID]

Well, give mbarron a star then.......