dynamic named range / expand until 1st #N/A?

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
185
Hi all - hoping for a little help with this.

Currently I use the following formula to create a dynamic named range that will expand down until the end of my data:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

But I want to adjust this formula to expand down to the first #N/A instead of all the way to the end. Is this possible?

Thanks in advance
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
try this. to test hit control+G and copy this function in the reference box and see whether the relevant data is highlighted


OFFSET($A$1,0,0,COUNTA(A:A)-COUNT(A:A,ISERROR(A:A)),1)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hi all - hoping for a little help with this.

Currently I use the following formula to create a dynamic named range that will expand down until the end of my data:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

But I want to adjust this formula to expand down to the first #N/A instead of all the way to the end. Is this possible?

Thanks in advance
Is there any non-error data after the first #N/A?

Is column A supposed to house text or numeric values?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
You could use this non-volatile version

=INDEX(Sheet1!$A:$A,1,1):INDEX(Sheet1!$A:$A, MIN(IF(ISNA(Sheet1!$A$1:$A$1000), ROW(Sheet1!$A$1:$A$1000))), 1)

If you do not want the last cell in the named range to contain #N/A, subtract 1

=INDEX(Sheet1!$A:$A,1,1):INDEX(Sheet1!$A:$A, MIN(IF(ISNA(Sheet1!$A$1:$A$1000), ROW(Sheet1!$A$1:$A$1000))) - 1, 1)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top