Dynamic Ranges that do not use COUNTA($A:$A)
 

Hi
try:
=OFFSET(r_A, 0, 0, COUNTA($C:$C)-CELL("row",r_A), 1)

--
Regards
Frank Kabel
Frankfurt, Germany


Joe wrote:
I am using XL 2002.

I have a list of items in a worksheet (say in cells C3:C7). In C1, I
have the columns header and C2 is blank.

I have created a name that refers to the first item's cell (C3).
Let's say that this is called r_A (the r_ stands for 'reference'). I
now need to create a dynamic range that refer to the values in the
list. I don't want to use

=OFFSET(r_A, 0, 0, COUNTA($C:$C), 1)

because I don't want to include blank cells (since I have a value in
C1), the range that this name would refer to would extend past the
list by one cell. I also do not want to hardcode an offset value.
As a programmer, I hate offset values.

The list will be populated using automation from an Access DB.

I would appreciate any ideas that you may have.

Thanks in advance,