Naming a dynamic range with blanks

Question

I have a range/list with values per row. Some of the rows are blank. I want to name the range, and the range should reach the last value (nonblank cell).

So if any cells below the range that become populated, then the range must expand to those cells, even if there are blanks between, like in scenario 2.

Do anyone have a suggestion to how to solve this?

Answer 1

If the non-blank entries in that column are numeric, you can use:

$M$5:INDEX($M:$M,MATCH(88^88,$M:$M))

where 88^88 is assumed to be larger than any numeric within the range.

If the non-blank entries in that column are non-numeric, and none of the blank entries are in fact null strings (""), you can use:

$M$5:INDEX($M:$M,MATCH("Ω",$M:$M))

Answer 2

Try below formula which work both for number and text strings.

=Sheet1!A2:INDEX(Sheet1!A:A,MAX(IF(Sheet1!A:A<>"",ROW(Sheet1!A:A),0)))

If you are on Excel-365 then try-

=Sheet1!A2:INDEX(Sheet1!A:A,MAX(FILTER(ROW(Sheet1!A:A),Sheet1!A:A<>"")))