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.
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.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
=AND(ISNA(MATCH(Target, DestinationList, 0)), LEN(Target)>0)
Thanks to all for the responses.
-RK