Problem with Dynamic Named Ranges

  • Thread starter Thread starter Andibevan
  • Start date Start date
A

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
 
Hi Andibevan,
From my understanding you can't physically select a dynamically named
range so it is hard to tell what it is selecting
Click to expand...

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
 
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
 
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)
 
Thanks Guys - much appreciated.

Duke Carey said:
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)
Click to expand...
 
Back
Top