Problem with Dynamic Named Ranges

[Andibevan]

Hi All,

I am having problems with dynamic named ranges.

On my table I have defect ID's (numerical) running from a2:a200 and the
title"Defect ID" in cell A1

I am using the following formula to dynamically name a range:-

=OFFSET($A$1,0,0,COUNT($A:$A),1)

Will this format of offset select A1:A200 as the named range or A2:200?

From my understanding you can't physically select a dynamically named range
so it is hard to tell what it is selecting

Thanks

Andi

 

[arno]

Hi Andibevan,

From my understanding you can't physically select a dynamically named
range so it is hard to tell what it is selecting

you _can_ see the range when editing the formula in (translated from
german):
Insert/Name/Define select your name and place the cursor in the formula
"refers to" at the bottom, then you will see "ants" running around your
range (wow, good english...)

otherwise run a little makro to higlight "myrange"

Sub Makro1()
Application.Goto Reference:="myrange"
End Sub

arno

 

[Roger Govier]

Hi Andi

It would be A1:A200 provided there are no gaps in the data. Count would
return 199, as it would ignore the text in A1.

When you use Insert>Name>Define, at the end of the white pane Refers
to, there is a small icon. Click this and it will show you the range
that would be selected by the criterium set.

Regards

Roger Govier

 

[Guest]

Press the F5 key, type in your dynamic range name, click on OK, and Excel
will select the range.

In your case, your defined range will NOT INCLUDE the last number in the
list. Either change the offset to reference cell A2, or add 1 to the
COUNT(A:A)

 

[Andibevan]

Thanks Guys - much appreciated.

Press the F5 key, type in your dynamic range name, click on OK, and Excel
will select the range.

In your case, your defined range will NOT INCLUDE the last number in the
list. Either change the offset to reference cell A2, or add 1 to the
COUNT(A:A)