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

[bluefish44]

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

 

[venkat1926]

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]

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]

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)