• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation

K

Kurt Smart

Member
Hi everyone, I'm trying to use data validation on a cell with a formula result but nothing seams to be happening...

Column Y has the formulas in giving result 'check' or 'pass'...

In column K I want to validate the cell so if You equals 'Check' then K cell needs to be greater than zero.
 
I think you need to provide more information.
What happens when Y = pass, what values are accepted? What if Y has no value or it has another value?

With only the information you provided you can validate K with the formula
=AND(Y2="check", ISNUMBER(K2), K2>0)
 
Hi this doesn't work if i enter a number higher than zero im getting a stop validation message...Does this mean it needs to know what happens when cell = Pass too?

If cell is pass i want numbers to be greater than 0
 
=AND(OR(Y2="check", Y2="pass"), ISNUMBER(K2), K2>0)

Here Y can be check or pass, and the number must be higher than zero.
Is this what you want?
 
So if it's a "pass" then no action needs to be taken but when that cell changes to "check" I need a validation that only a number greater than zero should be in this cell
 
Would you mind taking a look at this workbook (its is a trimmed down version with only what you need to see available).

So i've decided now to put data validation on the 'Approve' column checking that cells equal "Pass".

If those cells change to "Check" I want it to stop the user and tell them something's not right.

I have a custom validation setup on them which when I manually type "Check" into 'Approve' its does stop me exactly what I want but when the formula is doing this its not working

Any ideas or solutions much appricated
 

Attachments

  • Book1.xlsx
    121.8 KB · Views: 3
Validation will only fire when you confirm cell value via manual entry. This is likely due to data validation being tied to Worksheet_Change event under the hood. Formula and Validation shouldn't exist in same cell.

If you want to warn users, use CF to flag items that need review. If you want to stop user from moving further, you'll need VBA.
 
Yes I know that from your formula.

But when should validation be applied?
1. At data entry time on each of 4 columns?
2. Or only after all 4 columns have some value in it?
My recommendation is 1st one.

And what action should code perform after validation failed?
Clear content and force focus back onto the offending cell? Along with message box?

You'll need to give me full detail of what you want. Or I won't be able to give you code that fits your need.
 
As above is spot on... So the user can not leave a zero in those cells or leave it blank and likevyouvsay first option would suit best
 
Something like below in Sheet2 Worksheet module.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("InProgress[[Pallets]:[Units]]")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Column > 2 Then
        If Len(Target.Offset(, -1).Value) = 0 Then
            MsgBox "Previous cell to left must be filled first"
            Target.ClearContents
            Target.Offset(, -1).Activate
        ElseIf Not IsNumeric(Target.Value) Then
            MsgBox "Value must be number"
            Target.ClearContents
            Target.Activate
        ElseIf Target.Value <= 0 Then
            MsgBox "Value must be greater than zero"
            Target.ClearContents
            Target.Activate
        End If
    Else
        If Not IsNumeric(Target.Value) Then
            MsgBox "Value must be number"
            Target.ClearContents
            Target.Activate
        End If
    End If
End If
Application.EnableEvents = True
End Sub
 
You must log in or register to reply here.
Back
Top