Dynamic Range - ignoring formulas that result in empty output

[clumzyfish]

I'm trying to create a dynamic range using the a similar formula to the following - =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),2)

In the A column, is a list of formulas that either result in the showing a number or the text BLANK. Is there a way to make the dynamic range only to display the cells up to the text BLANK?

I tried using COUNTIF(DATA!$A:$A,"<>*BLANK*"), but it's still selecting the whole column for the range.

 

[jbeaucaire]

This formula will create a named range that includes all data from A1 down to the FIRST BLANK cell, any data below that will not be included:

=Sheet1!$A$2: INDEX(Data!$A:$A,MATCH(1,1/(Data!$A$1:$A$1000=""),0)-1)

 

[clumzyfish]

thanks! this works beautifully for column A, but how would i include column B as well? I tried messing around with the formula and couldn't get it to work.

 

[clumzyfish]

nevermind, i figured it out:

Sheet1!$A$2:$B$2:INDEX(Data!$A:$A,MATCH(1,1/(Data!$A$1:$A$1000=""),0)-1)

thanks again!