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