How can using user defined function in custom data validation?

Question

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.

I think you can't, see this post bellow: superuser.com/questions/797053/… — ebelair
– ebelair, Commented Aug 16, 2017 at 9:51
I don't know, maybe the guy who down-vote think you did not make enough researches ... — ebelair
– ebelair, Commented Aug 16, 2017 at 9:57
Upvoted as the question is both reasonable and clear to me. :) — Rory
– Rory, Commented Aug 16, 2017 at 10:02
Above answer not cover my question. That answer is: The user-defined function must return a Range not an array of value. and in above, my user defined function returns not an array of value. — mgae2m
– mgae2m, Commented Aug 16, 2017 at 10:02

Rory · Accepted Answer · 2017-08-16 10:00:45Z

2

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.

answered Aug 16, 2017 at 10:00

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

mgae2m Over a year ago

Problem. I want apply the validation for the column of a Table. But when using above, IsValid "Refer to" code, not extend from B1 to B2 and ... for each column cell of respective Table. This way works only for single cell, that defined in S2M(B2)<>"Error"

Rory Over a year ago

No, if you entered it as specified, it will adjust for each row.

mgae2m Over a year ago

Greatly thanks. That would be awesome. May I remove above comment, or its better to stay?

mgae2m Over a year ago

Why Ignore blank should be uncheck? That is an important point. Whats the reason of unchecking that, related to this?

Rory Over a year ago

I don't mind either way. If you don't uncheck it, it won't work. I think it's a bug.

|

Collectives™ on Stack Overflow

How can using user defined function in custom data validation?

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related