CODEWITHSUNDEEP
vbaexcelexcel-formula
mgae2m
mgae2m

Reputation: 1142

How can using user defined function in custom data validation?

In my Worksheet I have a Table and want to define Data validation for a column that contained the date, as bellow:

=S2M(B2)<>"Error"

In above, S2M() is a user defined function for converting date from Persian date to Gregorian date, because checking input date is right.

But excel is not letting me use user defined functions in Custom Data validation.

This error shows: A named range you specified cannot be found.

Please note that I was using bellow code in Custom Data validation and that works, right.

=AND(LEN(B2)=10;ISNUMBER(IFERROR(VALUE(MATCH(VALUE(MID(B2;1;4));INDIRECT("intTable[Year]");0)&MATCH(MID(B2;6;2);INDIRECT("intTable[Mounth]");0)&MATCH(MID(B2;9;2);INDIRECT("intTable[Day]");0));FALSE)))

Explain is a Persian date example: 1396/05/25

Thanks.

Upvotes: 3

Views: 1054

Answers (1)

Rory
Rory

Reputation: 34055

You can do that. Select B2, or whichever cell in row 2 you want the validation to apply to. Now define a name called, say, IsValid, using:

=S2M(B2)<>"Error"

Now in the data validation box, all you need to enter is:

=IsValid

in the source box and make sure to uncheck the Ignore Blank option.

Upvotes: 2

Related Questions