Link to home
Start Free TrialLog in
Avatar of busapps
busapps

asked on

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.
Avatar of roger_karam
roger_karam
Flag of Brazil image

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

-RK
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
Avatar of Rory Archibald
There is an option on the Data Validation menu to circle invalid data. No formulas or VBA required. :)
PS It's on the Formula Auditing toolbar pre 2007.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of busapps
busapps

ASKER

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.