Named Range formula corruption when deleting information from range

Hi guys

I am trying to understand Named Ranges and have come up against a propblem I can't understand, when I delete items from the range....

I have this formula in the named range...

=OFFSET(Data!$N$9,0,0,COUNTA(Data!$N$9:$N$9999),8)

I have 2 issues:

1.When I delete the data in the row for $N$9 it is corrupting the named range... Deleting from row 10 is not...

=OFFSET(Data!#REF!,0,0,COUNTA(Data!$N$9:$N$9898),8)

2. When the rows are being deleted the part of the range $N$9999 is changing down..

Haw can I prevent these happening...

Much appreciate any help

Many Thanks

Jim

If column N is text try replacing the COUNTA for the rows count with

Formula:

Please Login or Register  to view this content.

If column N is numeric

Formula:

Please Login or Register  to view this content.

These match presence of data in the last row ... with the understanding that the remaining data in column N is not returned by formula that returns "" empty strings.

Please try

=OFFSET(Data!$N$1,8,0,COUNTA(Data!$N:$N)-COUNTA(Data!$N$1:$N$8),8)

hope that you will not delete N1.