How can my formula handle inserted rows?

[Houston]

I am using (more likely misusing) COUNTA to validate that all cells within
the specified range are populated, but I have a question regarding how to
handle inserted rows. Here is my current formula:

=IF(COUNTA(A1:A10, B1:B10)=20,"All cells within specified range are
populated")

However, if someone inserts a row between the 1 and 10, the contents in row
10 get shoved to row 11, and I would want to expand my formula range
dynamically to accomodate this insert. How would I accomplish this? Thanks
for any insight!

 

[Dave Peterson]

If that range were simpler--just a single area, then the solution is easier.

And since you're really just counting the cells in A1:B10, you could use:

=if(COUNTA(A1:B10)=20, ....

And if you buy into that, you could use:

=if(counta(a1:b10)=(rows(a1:b10)*columns(a1:b10)), "all filled","not all")

 

[Ron@Buy]

Possible solution:
=IF(COUNTBLANK(A$1:INDEX(B:B,ROW()-1))>=1,"There are empty cells within the
specified range","All cells within specified range are populated")

 

[Houston]

Cool, these are both interesting solutions. Thanks to both of you!