I have the following formula, which counts a range of rows by finding the next non-blank cell in the column. It's entered as an array formula and works exactly as I'd like.
=MATCH(FALSE,ISBLANK(A7:A700),0)+1
This is entered in A6, so A7 is the cell below. The "700" of A700 is arbitrary.
I need to use the formula in a number of places, so I need to generate the address for ROW()+1, COLUMN(). I've tried a couple of approaches, but keep getting an error.
Trying to generate just the address for A7:
=MATCH(FALSE,ISBLANK(ADDRESS(ROW()+1,COLUMN()):A700),0)+1
And trying to generate a suitable range using ADDRESS() and OFFSET():
=MATCH(FALSE,ISBLANK(OFFSET(ADDRESS(ROW(),COLUMN()),1,0,700,1)),0)+1
Bookmarks