software

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: www.excelforum.com/member.php...oamp;userid=31214
View this thread: www.excelforum.com/showthread...hreadid=518191If 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)

quot;coa01gsbquot; wrote:

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


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: www.excelforum.com/member.php...oamp;userid=31214
View this thread: www.excelforum.com/showthread...hreadid=518191I 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,,quot;POS1quot;)) 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,,quot;POS1quot;)),0,0,COUNTA (OFFSET(POS1!$A:$A,0,10)-1))

Does this help?

Kostis VezeridesAre 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(quot;POS1!$K$2quot;),0,0,COUNTA(POS1!$K:$ K)-1)
I didn't test this, but it's close

quot;coa01gsbquot; wrote:

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