Data Validation using ISBLANK with non-contigious cells


Harry Flashman

I have a range of non-contigious cells in Column A (named range -
Job1), and a range of of non-contigious cells in Column B (named range
- Job2).
How can I set up data validation to make it impossible to enter any
values in the range Job1 if there anything entered in any of the cells
in the range Job2.
I can use data validation for single cells easily enough. For example
if I want to stop people entering data in A1 if there is data in B2 by
using data validation/custom, formula =ISBLANK(B2). This works so I
thought I would extend it to the entire range. Thus I selected all the
cells in Job1 and applied data validation thus: =ISBLANK(Job2), but
this did not work. Then I realized that ISBLANK does not work with non-
contigous cells, so I tried messing around with COUNTA(Job2)=0 but
this did not work either.
Would anyone be able to help me please?
[Btw this is a simplified example, in fact these ranges appear on
separate worksheets.]

Harry Flashman

For the cells in Job1 I used the following data validation:
And I unticked the box that said "ignore blank"
It worked.

Shane Devenshire


Most excel functions don't support non-contiguous ranges. That is the
problem here. In an empty cell enter the formula

Highlignt the Job1 range and choose Data, Validation, Custom and enter the


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question