prevent more than one value in a cell
 

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(MA TCH("andrew",K15:K16,0),J25:K29,2),IF(ISNUMBER(MAT CH("am",K19:K21,0)),VLOOKUP(MATCH("am",K19:K21,0), J25:L29,3),""))


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=380377

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(A1 5: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


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=380377

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"andrewm" wrote in
message ...

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(MA TCH("andrew",K15:K16,0),J2
5:K29,2),IF(ISNUMBER(MATCH("am",K19:K21,0)),VLOOKU P(MATCH("am",K19:K21,0),J2
5:L29,3),""))


--
andrewm
------------------------------------------------------------------------
andrewm's Profile:
http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=380377

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


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=380377