Combining conditions for data entry validation

R

Richard H Knoff

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
 
F

Frank Kabel

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)))
 
R

Richard H Knoff

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
 
F

Frank Kabel

Hi
are you using the semicolon as delimiter?. If yes replace the comas
with a semicolon
 
R

Richard H Knoff

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
 
M

Max

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)
 
A

Aladin Akyurek

=MATCH(Restrict&"*",A1,0)*(1-ISNUMBER(SEARCH("~?",A1)))

where A1 is the cell you want to custom data validate and Restric
refers to a cell which houses a value like "abc" or "a".
 
R

Richard H Knoff

Aladin,
thank you! It appears there are several ways to accomplish this.

Regards,
Richard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Validation With Date Range 2
Usage of Data Validation with If Condition 4
Conditional Formatting Colour and Text Entries 0
Data Validation - Only Allowing certain characters 1
Combine Formula and Data Validation List in a Single Cell ?? 3
Data validation: want input restricted to date value or N/A 2
sorting and adding cells in different work sheet? (hard stuff) 8
Validation problem. 3

Top