If you're deleting only cells in col K, change the offset to reference col J
or L, i.e.,

=OFFSET(POS1!$J$2,0,1,COUNTA(POS1!$K:$K)-1)

=OFFSET(POS1!$L$2,0,-1,COUNTA(POS1!$K:$K)-1)

"coa01gsb" wrote:

>
> Hi all,
>
> I have set up some dynamic labels, the only way I know how using the
> following formula
>
> =OFFSET(POS1!$K$2,0,0,COUNTA(POS1!$K:$K)-1)
>
> I know the COUNTA bit, returns a number which is equivalent to the
> number of non-blank cells in column K.
>
> So if in my spreadsheet each cell in column K from K1 to K200 contains
> data COUNTA will return 200. If I then delete the last 10 cells in
> column K, COUNTA will return 190, so the name attached to the above
> formula will refer to the range of cells K2:K190
>
> And now for the problem: However if I delete cells K2-K10. My formula
> no longer works, the $K$2 bit is replaced with REF!.
>
> So if you have understood me, how do I change my formula so that I can
> delete cells at the start of the range, but the formula will still
> reference all the consecutive cells in column K that contain data?
>
>
> --
> coa01gsb
> ------------------------------------------------------------------------
> coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
> View this thread: http://www.excelforum.com/showthread...hreadid=518191
>
>