PDA

View Full Version : Validation



souza.eq
06-13-2013, 11:19 AM
I need to validate an information like this
__A____B_C
Name 1 w w ....
Name 2 w w ....
Name 3 o o ....
Name 1 o w.
Name 2 w w.
so I need to do a conditional format showing (color or text box) the user that he can't use "w" again to "Name 1" or "Name 2", becouse he already chose "w" before and he can't choose again, for either column from B to L and lines 1 to 5 for example.
I thought use a data validation using countif with lookup, but i couldn't make it. I'm trying to write VBA right now, but I still think there is a easier and simpler way! (I'm storing the first name and running a loop to compare if there is another equal name then check if there is the same entry on the same column.

Thank you

mikerickson
06-15-2013, 10:56 AM
Try putting this validation on B2. and then copy the validation to the other cells/columns.

=SUMPRODUCT(($A$1:$A2=$A2)*(B$1:B2=B2))=1