How can I test for nulls within a range without specifying each ce

[Houston]

This is my (disgustingly beginner's) function:

=IF(AND('Worksheet2'!A5:A12="", 'Worksheet2'!B5:B12=""), "Both Columns
Null", IF(AND('Worksheet2'!A5:A12<>"", 'Worksheet2'!B5:B12=""), "All of
column A range populated", "Some of Column B range populated"))

The important part is where I am specifying the range in a different
worksheet ('Worksheet2'!A5:A12="") and testing the cells for nulls. The rest
of the function is just retesting the same cells for different null
conditions and displaying different values in the field dependant on the
results. I am assuming that when I specify the range, Excel first calculates
the total value of the range, then tests that total value for a null?

If this assumption is correct, how can I tell Excel to test each cell on a
different worksheet within a range for a null value, without doing the
painstaking A5="", A6="", A7="", A8="", etc. Terribly error prone and a
maintenance nightmare, tell me what I'm doing wrong!

 

[Dave Peterson]

=counta(a5:a12)
will return the number of non-empty cells in A5:A12 -- even if the cells contain
formulas that evaluate to "" (empty strings).

So

=if(counta(a5:a12)=0,"All 8 cells are empty","At least one is non-empty")

If I wanted to check to see if all 8 cells are filled:

=if(counta(a5:a12)=8,"all filled","not all filled")

 

[Houston]

Excellent, that's a much more efficient way of doing things. Thanks!!