• 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.

Data validation

emmatm

emmatm

Member
Hi - I have a cell where I have used the data validation function to allow the user to choose a selection from a dropdown.
Once this selection has been made I would like for the cell next to it to be updated with some information, depending what has been chosen on the dropdown.

So if one of the options on the dropdown is 'credit card' I would like for the next cell to ALWAYS be completed with only 16 digits, however if a different option was chosen thenit may a combination of alpha and numerics.

Do I need a table on the worksheet to say what criteria each drodown must have and how to I mandate this?

Thanks
 
Ok

AIDA/Conferma - 16 numerics
BACS - 10 numerics
Cheque - 5 numerics
Direct debit - 2 alpha 6 numerics
Team Credit cards - 16 numerics

Is this enough information?
 
Hi,

If your list selection in cell B2 then to create a validation in cell C2

Insert data validation and click custom and under formula copy paste the below formula

=OR(AND(B2="AIDA/Conferma",LEN(C2)=16,ISNUMBER(C2)),AND(B2="S",LEN(C2)=10,ISNUMBER(C2)),AND(B2="Cheque",LEN(C2)=5,ISNUMBER(C2)),AND(B2="Direct debit",LEN(C2)=8),AND(B2="Team Credit cards",LEN(C2)=16),ISNUMBER(C2))

I believe this would address your requirement
 
Thank you however this isnt working as I expected, I have chosen the first dropdown AIDA/Conferma and I am expecting to only allow 16 digits.
I cannot enter any alpha text which is good but I can only use 1 digit and it is accepting this, I want it to only accept 16 digits!
Do I need to change anything?
 
Hi,

Just a closing bracket issue. Try below formula

=OR(AND(B2="AIDA/Conferma",LEN(C2)=16,ISNUMBER(C2)),AND(B2="BACS",LEN(C2)=10,ISNUMBER(C2)),AND(B2="Cheque",LEN(C2)=5,ISNUMBER(C2)),AND(B2="Direct debit",LEN(C2)=8),AND(B2="Team Credit cards",LEN(C2)=16,ISNUMBER(C2)))
 
Hi,

I was able to copy pase this formula to the data validation.

You need to select Custom and copy paste the formula
 
Thanks Sathish this is better only thing it isnt doing is for the option Direct Debit I want it to take 2letters then 6 numerics
 
Refer the formula to a worksheet cell than in DV custom formula box just put =Cell Address with the formula (e.g. $A$1)

Regards,
 
Hi,

Create a list and use the below formula in the data validation

Note: I have created the list in range A7:B11. Change the range in below formula according to your list

=OR(AND(B2<>"Direct debit",B2=VLOOKUP($B$2,$A$7:$A$11,1,0),LEN(C2)=VLOOKUP($B$2,$A$7:$B$11,2,0),ISNUMBER(C2)),AND(B2="Direct debit",LEN(C2)=8,CODE(LEFT(C2,1))>=65,CODE(MID(C2,2,1))>=65,ISNUMBER(INT(RIGHT(C2,6)))))
 
Thanks both for your help, this has worked now.
Although I now have a different problem where my credit card number is 16 digits long the last digit is changing to a zero! I know I can format the cells to change the cell to a text instead of number but this will intefere with the formula - any ideas??
 
this then makes the characters more than 16 and gives me a false validation.
I can change the value in helper cell L4 to 17, is there anything else in the formula that needs to be changed?
 
Hi,

Keep the format as Text and use the below formula in the datavalidation

=OR(AND(B2<>"Direct debit",B2=VLOOKUP($B$2,$A$7:$A$11,1,0),LEN(C2)=VLOOKUP($B$2,$A$7:$B$11,2,0),ISNUMBER(INT(C2))),AND(B2="Direct debit",LEN(C2)=8,CODE(LEFT(C2,1))>=65,CODE(MID(C2,2,1))>=65,ISNUMBER(INT(RIGHT(C2,6)))))
 
You must log in or register to reply here.
Back
Top