Using custom functions within custom validation

[Neil]

When creating a custom validation rule using DATA-VALIDATION-CUSTOM menu, is
it possible to use a custom function that I have written?

E.g. =customfunc(A1)=true

Whenever I try this I get the error "A named range you specified cannot be
found". Presumably it is referring to the custom function name? Surely it is
possible?

Thanks for any help with this.

Neil

 

[Peo Sjoblom]

You need to refer to it indirectly, you can put it away somewhere not
normally visible like in IV1 then refer to IV1 or create a defined name
and refer to that name

 

[Neil]

Thanks Peo, but I am not sure that I fully understand you.

What exactly do you mean by referring to it indirectly and what is IV1 or a
defined name?

Thanks
Neil

 

[Peo Sjoblom]

OK, assume you want to validate an entry in A1 using a custom function, so
instead you can do insert>name>define and call it something
MyFunction
in the source box put

=customfunction(Sheet1!$A$1)

then in data>validation>custom use

=MyFunction=TRUE

make sure ignore blanks is not checked and it should work

or use another cell somewhere not visible (I chose IV1 since it is away of
the normal display) in IV4 put

=customfunction($A$1)

then in the data>validation use

=$IV$1=TRUE

 

[Neil]

Thankyou Peo,
That makes sense and it works now.
Cheers!
Neil