Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell addressing using the content of another cell. | Excel Worksheet Functions | |||
How do I prevent Excel from plotting a cell w/ a formula as a "ze. | Charts and Charting in Excel | |||
How do I prevent Excel from plotting a cell w/ a formula as a "ze. | Charts and Charting in Excel | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |