March 10, 2016
The attachment has 2 issues -
The DATA sheet has a column with numbers
In Sheet1 issue in column A - I enter data
I need Excel not to allow a number that exists in a DATA sheet to be typed
I set the following formula in data validation -
= COUNTIF (DATA! A: A, A2) = 0
And it does not work, why?
December 7, 2016
Your data validaton rule in cell A1 is =COUNTIF(DATA!A:A,A2)=0, which means that as long as cell A2 is empty or have allowed vaule (one that does not exist in your DATA sheet) you can type in whatever you want in A1.
Simply change the rule for A1 to =COUNTIF(DATA!A:A,A1)=0 and it will work as you intended.
The following users say thank you to Anders Sehlstedt for this useful post:Philip Treacy