Dynamic Named Range exclude last row

alexx579

New Member
Joined
Aug 19, 2014
Messages
42
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,046
If Column A contains text values, try...

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

Code:
=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!
 
Last edited:

Forum statistics

Threads
1,086,206
Messages
5,388,423
Members
402,116
Latest member
Chaskon

Some videos you may like

This Week's Hot Topics

Top