prevent more than one value in a cell

[andrewm]

Hi I am making a roster with the use of lookup (see below) - names say
"andrew" and "am".
I want only one name in either a1:a10 or a15:a20 either "andrew" or
"am", but not both - is it a validation or vba or help

andrew m

=IF(ISNUMBER(MATCH("andrew",K15:K16,0)),VLOOKUP(MATCH("andrew",K15:K16,0),J25:K29,2),IF(ISNUMBER(MATCH("am",K19:K21,0)),VLOOKUP(MATCH("am",K19:K21,0),J25:L29,3),""))

 

[olasa]

As I interpreted it..."andrew" or "am" but only One, shall be in the
range:

To check the range:
=IF(SUM(COUNTIF(A1:A10,{"andrew";"am"})+COUNTIF(A15:A20,{"andrew";"am"}))=1,"Correct","Wrong")

To do a Validation: (Allow:Custom)
SUM(COUNTIF($A$1:$A$10,{"andrew";"am"})+COUNTIF($A$15:$A$20,{"andrew";"am"}))=1

Hope it helped
Ola Sandström

 

[Ragdyer]

I don't understand exactly what you're looking for, particularly when you
mention A1:A10 and A15:A20?!?!

Since no one else has ventured a suggestion, you can assume that your post
is confusing to *everyone*.

Staying in this thread, try explaining in a different manner, exactly what
you're looking for.

 

[andrewm]

Hi,

yes thats right "andrew" or "am" but only one shall be in the range
however, when I use the range idea it works with if(sum etc.
but I must be doing a validation wrong as it doesn't work

I use a dropdown list to put names in the range of cells. how can I
make sure I only put down "andrew" or "am", but not both in the range
with use of a dropdown list

thanks andrewm