• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I know this is incorrect - Help with the correction - If Statement

F

Frank Bacchus

Member
In cell C5 (and the C column), users are inputting the follow (example), "CM123456789". I want to validate two things, (1) that the information entered in that cell (C5) is (1) preceded with "CM" and (2) after the CM, the following 9 characters are numeric.

I have a data validation for the CM part but not for the numeric part. I would like to put it all in one formula. C5 is an input cell, so the user is entering the full CM number. After they enter this number then the input needs to be validate, to see if it is correct. Thanks.

frank
 
Bosco_yip:

Thanks. when data is entered in C4 or C5 I want to force the user to select an entry in C6 (C6 is a dropdown). They must select an item.
 
Frank Bacchus said:
Bosco_yip:

Thanks. How does this get invoked once the user enters the data in C5?

frank
Click to expand...

I played a bit for my understanding. If you enter smth different than CM or less numbers than 9, the cell shouts that the value you entered is not valid. A person can't get through in this cell untill he enters CM and 9 numbers.

Anyway, for me it is not clear how you will validate if he mixes the numbers by accident. Let's say he needs to enter CM553553553. But he enters CM555553553 (one 3 becomes 5).
 
Thanks Siga:

If the input is formatted correctly but the data is incorrect (like your example above), that will be reviewed thru a downstream process. My issue is becoming a little more complicated as I have another cell (C4) with a similar pattern but the numbers part can be 3 or 4 digits. I am trying to use bosco_yip technique (above) but I can figure out how to accommodate for the variable lengths. C4 can contain DKE-449 or DKE-1449. How do I use this Data Validation method to ensure that the number part of the data are numeric. Here is the data validation formula: =(LEN(C4)=4)*(LEFT(C4,4)="DKE-")*ISNUMBER(--RIGHT(C4,4)). The problem here is it is not good for the 3 digit input. Any suggestions?

frank
 
Frank Bacchus said:
Thanks Siga:

If the input is formatted correctly but the data is incorrect (like your example above), that will be reviewed thru a downstream process. My issue is becoming a little more complicated as I have another cell (C4) with a similar pattern but the numbers part can be 3 or 4 digits. I am trying to use bosco_yip technique (above) but I can figure out how to accommodate for the variable lengths. C4 can contain DKE-449 or DKE-1449. How do I use this Data Validation method to ensure that the number part of the data are numeric. Here is the data validation formula: =(LEN(C4)=4)*(LEFT(C4,4)="DKE-")*ISNUMBER(--RIGHT(C4,4)). The problem here is it is not good for the 3 digit input. Any suggestions?

frank
Click to expand...

based on Bosco formula below
=(LEN(C5)=11)*(LEFT(C5,2)="CM")*ISNUMBER(--RIGHT(C5,9))

for 4 number digits the first part of the formula should be
=(LEN(C4)=8)
as you have 4 simbols as letters and 4 simbols as numbers: DKE-1449 (total 8)

how to make to work 3 and 4 together for numbers - let's hope Bosco will step in.
 
Siga said:
based on Bosco formula below
=(LEN(C5)=11)*(LEFT(C5,2)="CM")*ISNUMBER(--RIGHT(C5,9))

for 4 number digits the first part of the formula should be
=(LEN(C4)=8)
as you have 4 simbols as letters and 4 simbols as numbers: DKE-1449 (total 8)

how to make to work 3 and 4 together for numbers - let's hope Bosco will step in.
Click to expand...

Check C4 preceded with "DKE-" and, the following numbers part should be 3 or 4 digits

e.g. DKE-449 or DKE-1449

Data Validation, custom formula :

=(LEFT(C4,4)="DKE-")*(OR(LEN(MID(C4,5,20))={3,4}))*(ISNUMBER(-MID(C4,4,20)))

Regards
Bosco
 
bosco_yip said:
Check C4 preceded with "DKE-" and, the following numbers part should be 3 or 4 digits

e.g. DKE-449 or DKE-1449

Data Validation, custom formula :

=(LEFT(C4,4)="DKE-")*(OR(LEN(MID(C4,5,20))={3,4}))*(ISNUMBER(-MID(C4,4,20)))

Regards
Bosco
Click to expand...

Bosco - Can you please explain this validation. Some of the requirement have changed since. The C4 cell can contain the following - "DKE-449 or DKE-1449 or TKTS-1234". How do I change the custom formula to accommodate this?

frank
 
Hi ,

If you can make a list of acceptable entries , and enter them in a worksheet range of cells , it would make the validation formula much simpler.

Narayan
 
You must log in or register to reply here.
Back
Top