Solved

ISBLANK, Validation Rule

Posted on 2014-02-03
12
474 Views
Last Modified: 2014-02-12
Hello Experts,

Ok, I'm confused...

I am trying to setup a validation rule - a custom one...

Before I start setting up the validation,... I think a validation rule needs a TRUE/FALSE result.  So on a separate worksheet - I tested out the "=ISBLANK(A1:A2)" and it returns FALSE all the time.

What am I missing here?

I have a group of cells that I selected - they can only have values typed into them IF cells (named cells) "ShiftStart" & "ShiftEnd" have values.  If they don't, then the selected cells should NOT accept values.

Thank you in advance for your help!
0
Comment
Question by:Geekamo
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 12

Expert Comment

by:duttcom
ID: 39831561
ISBLANK is for checking a single cell, not a range. You could try the AND function-

=AND(ISBLANK(A1),ISBLANK(A2))

Which will return true only when both the cells are blank, false if either or or both contain values, so your validation checks for the false condition.

Edit-

Add a NOT statement so that the formula returns true (instead of false) when the two cells have values-

=NOT(AND(ISBLANK(A1),ISBLANK(A2)))
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39831575
Or try

=COUNTA(A1:A2)=0

or

=COUNTA(A1:A2)>0
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39831584
@ all,

Both great solutions!

@ duttcom,

Your solution worked first, butI do like @ Saqib Husain, Syed solution too.

Do you mind if I split the points?
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39831640
@ all,

Ok, I think I'm missing something.  In the test I was doing, your formulas work perfectly. But as I tried to apply it to where it needs to go - I can't get either solution working correctly.

See attached file.

If you select the named range:  CallDuration - you'll see that it selects all the required cells the validation is to be applied to.  And named cells ShiftStart & ShiftEnd are the cells the validation looks to.

If time values are entered in BOTH Start and End - then the CallDuration cells should accept values.  But if either of them are blank, no call duration should be accepted.

Any ideas what I'm doing wrong?
Talk-Time-Calculator---orig.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39834344
You wrote your data validation criteria to require ShiftStart and ShiftEnd to both be blank. Instead of using ISBLANK, try modifying Saqib's formula like this:
=COUNT(ShiftStart,ShiftEnd)=2

The COUNT function counts the number of cells containing numeric entries, such as times. By using the named ranges, you get an absolute reference to those cells as your data validation is copied down and across. And by requiring that COUNT return a value of 2, the error message will be displayed when the user enters a value and the COUNT is not 2.

Note that ISBLANK will be fooled by the user enter text like "x". That's why it is better to use COUNT for the data validation criteria. If you insist on using ISBLANK, you need to wrap the AND inside a NOT:
=NOT(AND(ISBLANK(ShiftStart),ISBLANK(ShiftEnd)))

I think COUNT is a lot cleaner way to do it.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39837677
@ byundt,

I'm not sure what I'm missing, but I copied and pasted:

=COUNT(ShiftStart,ShiftEnd)=2

And the cells are still allowing values, even without Start & End times.

Any ideas?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:Geekamo
ID: 39837681
@ all,

Ok, I think I know what the issue is.

The cells that I am applying the validation to, have VBA running on those cells in the background.  I have a feeling, the code is bypassing any validation rules I have setup?

Therefor, I think my only solution is to put a validation check at the beginning of the VBA code.

Jay
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39837746
I had trouble getting the data validation to work. So I moved its functionality into the Worksheet_Change sub.
Private Sub Worksheet_Change(ByVal target As Range)
Dim v As Variant
Dim cel As Range, targ As Range
If target.Rows.Count >= Rows.Count Then Exit Sub

Set targ = Range("B6:D105")     'Watch these cells for time entries
Set targ = Intersect(targ, target)
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cel In targ.Cells
    If IsNumeric(cel.Value) Then
        If cel.Value > 0 Then
            If Len(cel.Value) < 7 Then
                On Error Resume Next
                v = 0
                v = TimeValue(Format(cel.Value, "00:00:0#"))
                On Error GoTo 0
                If v = 0 Then
                    cel.Select
                    MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                    cel.ClearContents
                Else
                    If [COUNT(ShiftStart,ShiftEnd)] = 2 Then
                        cel.NumberFormat = "hh:mm:ss"
                        cel.Value = v
                    Else
                        targ.ClearContents
                        If Range("ShiftEnd") = "" Then Range("ShiftEnd").Select
                        If Range("ShiftStart") = "" Then Range("ShiftStart").Select
                        MsgBox "These fields only accept values after you have entered your Start and End time above."
                        Exit For
                    End If
                End If
            Else
                cel.Select
                MsgBox "Too many digits in " & cel.Value
                cel.ClearContents
            End If
        Else
            If cel.Value < 0 Then
                cel.Select
                MsgBox cel.Value & " is not a permissible time value"
                cel.ClearContents
            End If
        End If
    Else
        cel.Select
        MsgBox cel.Value & " is not a permissible time value"
        cel.ClearContents
    End If
Next
Application.EnableEvents = True

End Sub

Open in new window

Talk-Time-Calculator---origQ2835.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39837775
@ byundt,

You're genius! The solution you provided is working flawlessly!

Could you possibly suggest how I should be awarding points on this question?  Your solution worked, yet the others would have - had my VBA not been conflicting.

I think an even split between all involved is in order?

Please let me know your thoughts.

Thanks again!

~ Geekamo
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39837828
You should award points to THE solution that actually worked.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39837833
Saqib,
Are you able to get the Custom validation to work with Geekamo's workbook?

I tried in Excel 2013 and failed miserably. I know that your formula ought to work, duttcom's should work and mine should work. I even turned the data manipulation part of the Worksheet_Change sub off, to no avail.

Brad
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39839222
Hi Brad, I am out of town since yesterday and unable to test this. Maybe tomorrow.

Saqib
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now