Dynamic Labels

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?

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

Nope sorry deleting whole rows.

I was hoping there would be a function that I could replace $K$2 with that would just find the first cell in column K containing numerical data (i.e. ignoring the header row.

Are you ever going to delete the first row? Use K1 instead in that case.

The other way is to use the indirect function:

=OFFSET(indirect("POS1!$K$2"),0,0,COUNTA(POS1!$K:$K)-1)
I didn't test this, but it's close

"coa01gsb" wrote:

>
> Nope sorry deleting whole rows.
>
> I was hoping there would be a function that I could replace $K$2 with
> that would just find the first cell in column K containing numerical
> data (i.e. ignoring the header row.
>
>
> --
> coa01gsb
> ------------------------------------------------------------------------
> coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
> View this thread: http://www.excelforum.com/showthread...hreadid=518191
>
>

I should say you have to make all references offsets from a cell which
will not be deleted. Even better, you can use
INDIRECT(ADDRESS(2,11,,"POS1")) for K2, which will not bother if you
ever delete K2. Similarly, you can use OFFSET(POS1!$A:$A,0,10) instead
of POS1!$K:$K, although the full column reference should not be
bothered by deleting cells. So, you can try something like:

=OFFSET(INDIRECT(ADDRESS(2,11,,"POS1")),0,0,COUNTA(OFFSET(POS1!$A:$A,0,10)-1))

Does this help?

Kostis Vezerides