PDA

View Full Version : Validation On Columns



amit_nalawad
07-11-2009, 03:28 AM
Hi,

I want to create excel file which has columns with multiple validation.

Like 1st column should contain date with dd-mm-yyyy format. 2nd column should contain numbers with limit of 10 digit.

These columns should have headings Date and Number respectively.
Please help me with this.

Thanks
Amit

Bob Phillips
07-11-2009, 03:45 AM
What is your difficulty, DV does that simple enough.

amit_nalawad
07-11-2009, 03:51 AM
You are right but using DV I won't be able to give multiple validations for same column. Like if i want to have numbers in column A with maximum length of 10 digit. In this case DV does not work. If I want only alphabets in third column, i can't do this using DV.

mdmackillop
07-11-2009, 04:48 AM
You are right but using DV I won't be able to give multiple validations for same column. Like if i want to have numbers in column A with maximum length of 10 digit. In this case DV does not work. If I want only alphabets in third column, i can't do this using DV.
You mention the "same column", then talk about Column A and the "third column". You then add in new validations. I'm totally confused.

Bob Phillips
07-11-2009, 07:39 AM
You are right but using DV I won't be able to give multiple validations for same column. Like if i want to have numbers in column A with maximum length of 10 digit. In this case DV does not work.

Yes you can, custom type with a formula of

=AND(ISNUMBER(M1),LEN(M1)<=10)


If I want only alphabets in third column, i can't do this using DV.

Yes you can, custom type with a formula of

=SUMPRODUCT(--(MID(M1,ROW(INDIRECT("1:"&LEN(M1))),1)>="0"),--(MID(M1,ROW(INDIRECT("1:"&LEN(M1))),1)<="9"))=0

amit_nalawad
07-12-2009, 11:13 PM
Thank you so much. I never knew this before.