| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Combining conditions for data entry validation
I need to create a validation rule that goes like this:
(1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard |
|
#2
|
|||
|
|||
|
Hi Richard
try Cell A1 is your restirct cell and B1 the cell for which you want to apply this validation In the validation dialog enter =(LEFT(B1,LEN(A1))=A1)*NOT(ISNUMBER(FIND("?",B1))) -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: I need to create a validation rule that goes like this: (1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard |
|
#3
|
|||
|
|||
|
Frank,
thanks a lot for your prompt reply. I'm having problems, however, applying the validation rule. I get a "formula error" message when I try to apply the rule to B1. I also get the error message when I try to paste og type in part of the rule, like =LEFT(B1,LEN(A1))=A1 or =NOT(ISNUMBER(FIND("?",B1)) But =ISNUMBER(A1) is accepted. I've translated the formula terms to Norwegian, by the way. Any ideas?? Regards, Richard "Frank Kabel" wrote in : Hi Richard try Cell A1 is your restirct cell and B1 the cell for which you want to apply this validation In the validation dialog enter =(LEFT(B1,LEN(A1))=A1)*NOT(ISNUMBER(FIND("?",B1))) -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: I need to create a validation rule that goes like this: (1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard |
|
#4
|
|||
|
|||
|
Hi
are you using the semicolon as delimiter?. If yes replace the comas with a semicolon -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: Frank, thanks a lot for your prompt reply. I'm having problems, however, applying the validation rule. I get a "formula error" message when I try to apply the rule to B1. I also get the error message when I try to paste og type in part of the rule, like =LEFT(B1,LEN(A1))=A1 or =NOT(ISNUMBER(FIND("?",B1)) But =ISNUMBER(A1) is accepted. I've translated the formula terms to Norwegian, by the way. Any ideas?? Regards, Richard "Frank Kabel" wrote in : Hi Richard try Cell A1 is your restirct cell and B1 the cell for which you want to apply this validation In the validation dialog enter =(LEFT(B1,LEN(A1))=A1)*NOT(ISNUMBER(FIND("?",B1))) -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: I need to create a validation rule that goes like this: (1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard |
|
#5
|
|||
|
|||
|
Frank,
you were right, of course. I didn't notice the commas. The formula is accepted now, and does exactly what I wanted. Thank you very much! Regards, Richard "Frank Kabel" wrote in : Hi are you using the semicolon as delimiter?. If yes replace the comas with a semicolon -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: Frank, thanks a lot for your prompt reply. I'm having problems, however, applying the validation rule. I get a "formula error" message when I try to apply the rule to B1. I also get the error message when I try to paste og type in part of the rule, like =LEFT(B1,LEN(A1))=A1 or =NOT(ISNUMBER(FIND("?",B1)) But =ISNUMBER(A1) is accepted. I've translated the formula terms to Norwegian, by the way. Any ideas?? Regards, Richard "Frank Kabel" wrote in : Hi Richard try Cell A1 is your restirct cell and B1 the cell for which you want to apply this validation In the validation dialog enter =(LEFT(B1,LEN(A1))=A1)*NOT(ISNUMBER(FIND("?",B1))) |
|
#6
|
|||
|
|||
|
Try this slight adaptation of Frank's suggestion
which seems to work ok .. To validate input in say B1, set as the validation criteria: Allow Custom Formula: =(LEFT(B1,LEN(Restrict))=Restrict)*ISERROR(FIND("? ",$B$1)) (you'd need to translate the formula to suit your language version) -- Rgds Max xl 97 -- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom --- "Richard H Knoff" wrote in message 9... Frank, thanks a lot for your prompt reply. I'm having problems, however, applying the validation rule. I get a "formula error" message when I try to apply the rule to B1. I also get the error message when I try to paste og type in part of the rule, like =LEFT(B1,LEN(A1))=A1 or =NOT(ISNUMBER(FIND("?",B1)) But =ISNUMBER(A1) is accepted. I've translated the formula terms to Norwegian, by the way. Any ideas?? Regards, Richard "Frank Kabel" wrote in : Hi Richard try Cell A1 is your restirct cell and B1 the cell for which you want to apply this validation In the validation dialog enter =(LEFT(B1,LEN(A1))=A1)*NOT(ISNUMBER(FIND("?",B1))) -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: I need to create a validation rule that goes like this: (1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard |
|
#7
|
|||
|
|||
|
=MATCH(Restrict&"*",A1,0)*(1-ISNUMBER(SEARCH("~?",A1))) where A1 is the cell you want to custom data validate and Restrict refers to a cell which houses a value like "abc" or "a". Richard H Knoff Wrote: I need to create a validation rule that goes like this: (1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=278100 |
|
#8
|
|||
|
|||
|
Hi
thanks for your feedback -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: Frank, you were right, of course. I didn't notice the commas. The formula is accepted now, and does exactly what I wanted. Thank you very much! Regards, Richard "Frank Kabel" wrote in : Hi are you using the semicolon as delimiter?. If yes replace the comas with a semicolon -- Regards Frank Kabel Frankfurt, Germany Richard H Knoff wrote: Frank, thanks a lot for your prompt reply. I'm having problems, however, applying the validation rule. I get a "formula error" message when I try to apply the rule to B1. I also get the error message when I try to paste og type in part of the rule, like =LEFT(B1,LEN(A1))=A1 or =NOT(ISNUMBER(FIND("?",B1)) But =ISNUMBER(A1) is accepted. I've translated the formula terms to Norwegian, by the way. Any ideas?? Regards, Richard "Frank Kabel" wrote in : Hi Richard try Cell A1 is your restirct cell and B1 the cell for which you want to apply this validation In the validation dialog enter =(LEFT(B1,LEN(A1))=A1)*NOT(ISNUMBER(FIND("?",B1))) |
|
#9
|
|||
|
|||
|
Aladin,
thank you! It appears there are several ways to accomplish this. Regards, Richard Aladin Akyurek wrote in : =MATCH(Restrict&"*",A1,0)*(1-ISNUMBER(SEARCH("~?",A1))) where A1 is the cell you want to custom data validate and Restrict refers to a cell which houses a value like "abc" or "a". Richard H Knoff Wrote: I need to create a validation rule that goes like this: (1) Only accept values that start with the value in named cell "Restrict" (2) Do not accept values that contain a "?" (a question mark). Example: If the cell "Restrict" contains "abc", the cell with the validation rule should accept "abc", "abcd", "abc123" etc, but not "abc?", "abcd?", "aab" or anything else. If "Restrict" contains "a", the cell should accept anything starting with "a", unless there's a "?" involved. This should be possible, shouldn't it?? Richard |
|
#10
|
|||
|
|||
|
Max,
this is a nice variation! Thank you! Regards, Richard "Max" wrote in news:uYkhH8jyEHA.2572 @tk2msftngp13.phx.gbl: Try this slight adaptation of Frank's suggestion which seems to work ok .. To validate input in say B1, set as the validation criteria: Allow Custom Formula: =(LEFT(B1,LEN(Restrict))=Restrict)*ISERROR(FIND("? ",$B$1)) (you'd need to translate the formula to suit your language version) |
|
#11
|
|||
|
|||
|
You're welcome, Richard !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Richard H Knoff" wrote Max, this is a nice variation! Thank you! Regards, Richard |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation Window? | Excel Discussion (Misc queries) | |||
| Combining Defined Names to New Name For Validation | Excel Worksheet Functions | |||
| Combining Defined Names to New Name For Validation | Excel Worksheet Functions | |||
Linear Mode
