Dynamic Labels

[coa01gsb]

Hi all,

I have set up some dynamic labels, the only way I know how using th
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 th
number of non-blank cells in column K.

So if in my spreadsheet each cell in column K from K1 to K200 contain
data COUNTA will return 200. If I then delete the last 10 cells i
column K, COUNTA will return 190, so the name attached to the abov
formula will refer to the range of cells K2:K190

And now for the problem: However if I delete cells K2-K10. My formul
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 ca
delete cells at the start of the range, but the formula will stil
reference all the consecutive cells in column K that contain data

 

[Guest]

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]

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.

 

[vezerid]

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

 

[Guest]

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