Link to home
Start Free TrialLog in
Avatar of Geekamo
GeekamoFlag for United States of America

asked on

ISBLANK, Validation Rule

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!
Avatar of duttcom
duttcom
Flag of Australia image

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)))
Avatar of Saqib Husain
Or try

=COUNTA(A1:A2)=0

or

=COUNTA(A1:A2)>0
Avatar of Geekamo

ASKER

@ 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?
Avatar of Geekamo

ASKER

@ 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
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.
Avatar of Geekamo

ASKER

@ 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?
Avatar of Geekamo

ASKER

@ 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
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
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 Geekamo

ASKER

@ 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
You should award points to THE solution that actually worked.
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
Hi Brad, I am out of town since yesterday and unable to test this. Maybe tomorrow.

Saqib