I have three named ranges: Name, Status, Name_validation_list. The two first
are about 1000 rows long. I need to get a warning if someone tries to enter
two identical occurances or combinations - He "C" and "Stop" are occuring
more than one time. The warning may perhaps be built from the SUMPRODUCT
returning lager than 1. This is quite easily accomplished with an IF formula.
I want to return the warning in one cell only. Example: $C$1. The criterion
in the "Status" range is fixed: "Stop", but the criterion in the "Name" range
is not. Therefore I have to test the status "Stop" to any of the values in
the named range "Name_validation_list".

A formula testing only one argument is easy and would look like this:
=SUMPRODUCT((Name="C")*(Status="Stop")) Returning "2".
However I need to test against any value: A, B, C, D, ......etc. - as in the
named range Name_validation_list. I believe this could be done through an
array formula inside the SUMPRODUCT formula, but I can't figure it out. The
Name_validation_list is about 50 rows long, so doing one by one would be to
difficult. I also don't want to add another column to assist the calculation.

Name Status
X Started
B Started
C Stop
C Stop
D Stop



Name_validation_list
A
B
C
D
E
F

--
Thanks
John_J