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! :D

=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 wrote:

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! :D

--

Dave Peterson

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

"Dave Peterson" wrote:

=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 wrote:

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! :D

--

Dave Peterson
.