=AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2)

And to be consistent, the column refs don't need to be absolute:

=AND(A2<"",SUMPRODUCT(--(A$2:A2&"^^"&B$2:B2=A2&"^^"&B2))<2)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I interpret the post differently.

Supplier1...1 = ok
Supplier2...1 = ok
Supplier1...1 = not ok
.................1 = not ok

Different suppliers having the same invoice number should be a valid
entry. Although the chances of that happening might be pretty slim!

Assume the range to validate is B2:B10
Select the range B2:B10 starting from cell B2
DataValidation
Allow: Custom
Formula:

=AND($A2<"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2)

Uncheck Ignore blank
OK

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Correction to the formulas

=COUNTIF($B$2:$B$10,B2)=1

and for entire column

=COUNTIF(B:B,B1)=1

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Using data validation you can; but only manual entries/dropdown entries
will
be validated

--Select B2:B10
--Select menu DataValidation
--Select Custom from the 'Allow' dropdown and enter the below formula

=COUNTIF($B$2:$B$10,B$2)=1
--From the Error alert tab enter the message you want to be displayed
--Click OK and try.

If you are selecting the entire column the validatio would be
=COUNTIF(B:B,$B$1)=1


If this post helps click Yes
---------------
Jacob Skaria


"Michelle" wrote:

Hi!

How to set data validation function (in column B) to restrict user
from
entering same invoice number from same supplier?
Column A: Supplier
Column B: Supplier Invoice Number

TIA