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

#### bluefish44

##### Board Regular
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?

### 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
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)

##### MrExcel MVP
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?

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

Is column A supposed to house text or numeric values?

#### mikerickson

##### MrExcel MVP
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)

Replies
5
Views
36
Replies
0
Views
44
Replies
3
Views
300
Replies
6
Views
109
Replies
0
Views
43