If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can my formula handle inserted rows?
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! |
#2
|
|||
|
|||
How can my formula handle inserted rows?
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") Houston wrote: 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 |
#3
|
|||
|
|||
How can my formula handle inserted rows?
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" wrote: 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! |
#4
|
|||
|
|||
How can my formula handle inserted rows?
Cool, these are both interesting solutions. Thanks to both of you!
|
Thread Tools | |
Display Modes | |
|
|