Excel Validation function

In my Example I have a list of items on a separate sheet.  I have a data validation rule that says the items must be selected from my list.  I want to be able to run a formula in my Excel sheet that lets me know if the data in the cell is valid.  I cannot use any VBA code to do this because I have a new requirement  to not use any macros in this worksheet.  

Here is the VBA function I had written.

Public Function ISVALID(ByVal Target As Range) As Boolean
    ISVALID = Target.Validation.Value
End Function

However, I need a way to do this using the current Excel formulas.  Any help would be appreciated.
LVL 1
busappsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Use the MATCH function:

   =NOT(ISNA(MATCH(Target,ValidationList,0)))

Kevin
0
 
roger_karamCommented:
Do you have a sample sheet with what you are looking for? What is your validation rule?

-RK
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
I do not understand the need for this check.

If you have as validation a list the value you enter can not be other then in this list.

if you use the dropdown it is always valid.
If you type you can't type anything else then in the list so it is valid to.
Only if you want to prevent a blank, and that is a easy check..



Kind regards


Eric
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Rory ArchibaldCommented:
There is an option on the Data Validation menu to circle invalid data. No formulas or VBA required. :)
0
 
Rory ArchibaldCommented:
PS It's on the Formula Auditing toolbar pre 2007.
0
 
busappsAuthor Commented:
I ended up using the following formula into conditional formatting:  

=AND(ISNA(MATCH(Target, DestinationList, 0)), LEN(Target)>0)

Thanks to all for the responses.
0
All Courses

From novice to tech pro — start learning today.