Prev: How many entries can Excel handle!?
Next: speak cells
From: Houston on 25 Mar 2010 13:50
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
> .
>
 | 
Pages: 1
Prev: How many entries can Excel handle!?
Next: speak cells