Data Validation - Named range does not exists but no named range in formula?

[Ryeera]

Hello everyone on this forum,

I just made a custom function that allows me to check if a string does satisfy a regular expression. If I enter the formula into an empty cell, it works perfectly fine and returns a boolean. However, if I want to use the exact same formula in Data Validation, it gives me the error message "A named range you specified cannot be found".

This is driving me insane!

This is the formula I entered into the field: =REFIND(B5,"KS(ET|MB|WI|IT)[0-9]{2}")

It just does not contain a named range. Do custom formulas not work with Data Validation?

Thanks in advance for all the help!
- Ryeera

PS: I am using Excel 2010

 

[RoryA]

Welcome to the forum.

Do custom formulas not work with Data Validation?

No, not directly. You need to define a name using the formula you want, then refer to that name in the DV settings.

 

[Ryeera]

Welcome to the forum.



No, not directly. You need to define a name using the formula you want, then refer to that name in the DV settings.

Thanks :D

I solved it now by entering the formula into an empty cell in a hidden worksheet and then referring to that cell in the data validation.