ISBLANK() WITH MULTIPLE RANGES

[cheveley]

hi

does anyone know if it is possible to use the isblank() formula with multiple ranges i.e. isblank(h1:z40) returns false even if the cells are blank. once again i am in the dark.

thanks

 

[NBVC]

I don't think so,

But you can do something like this

(H1:Z40)="", confirmed with Ctrl+Shift+Enter, not just Enter.

 

[GlennUK]

Hi NBVC, that only gives the answer for the first cell in the range.

To find out if the entire range is blank, use:

=AND(ISBLANK(H1:Z40))

entered using Ctrl-Shift-Enter instead of Enter.

 

[cheveley]

works a treat, thanks.

 

[NBVC]

Hi NBVC, that only gives the answer for the first cell in the range.

To find out if the entire range is blank, use:

=AND(ISBLANK(H1:Z40))

entered using Ctrl-Shift-Enter instead of Enter.

Hi Glenn,

Although, I do admit I like your formula better, I re-tested my formula again and it works just like yours (i.e. If any cells are blank in the range, False response is returned and if All cells are blank a True is returned.)

 

[GlennUK]

Hi NBVC,

test your tests again. For your formula, if the first cell is blank a TRUE is returned, even if there are non-blank cells in the range. So, your formula does not work just like mine.

 

[cheveley]

how would one extend =AND(ISBLANK(H1:Z40)) to

=AND(ISBLANK(H1:Z40,aa430;ac450))

i.e. another range in the formula. is it possible.

thanks.

 

[NBVC]

Hi NBVC,

test your tests again. For your formula, if the first cell is blank a TRUE is returned, even if there are non-blank cells in the range. So, your formula does not work just like mine.

I guess you're right. Sorry. That's one I didn't try.

 

[NBVC]

how would one extend =AND(ISBLANK(H1:Z40)) to

=AND(ISBLANK(H1:Z40,aa430;ac450))

i.e. another range in the formula. is it possible.

thanks.

I would say: =AND(ISBLANK(H1:Z40),ISBLANK(AA430:AC450))

 

[GlennUK]

Hi cheveley, you extend the number of ranges tested by nesting the AND functions, like this:

=AND(AND(ISBLANK(F3:H3)),AND(ISBLANK(J2:L2)))

Hi NBVC,
no problem, just making sure that the right formula gets used.

Edited:
Hi NBVC,
your final post is great! I've got more ANDs than necessary.