Dynamic Named Range exclude last row

[alexx579]

Hi all,

I have a data set as follows:

Data Headers A1: U1

Data: A2:U395

The number of rows in the data changes every month, and so I have a dynamic named range I want to use (formula below) which exclude the last non-blank cell in the column. The problem is I need to apply it to the whole range of data!! Can anyone help me??

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

Thank You )))

 

[Domenic]

If Column A contains text values, try...

=Data!$A$2:INDEX(Data!$A:$U,MATCH(REPT("z",255),Data!$A:$A,1)-1,COLUMNS(Data!$A:$U))

or

=Data!$A$2:INDEX(Data!$A:$U,MATCH(REPT("z",255),Data!$A:$A,1)-1,0)

If Column A contains numerical values, replace REPT("z",255) with 9.99999999999999E+307.

Hope this helps!