Dynamic changing drop down

[smls]

I have the following code:

Please Login or Register  to view this content.

I tried this this and without the blank commas

I am trying to get it to automatically adjust my defined name range for a drop down since we add new events frequently enough. I have V1 set as the 'EventAnchor' named range but I can't figure out why it keeps returning 21 rows of events when I am testing with only 3 events.

Column V
V1: Events (just the title of the list and the anchor cell I am using)
V2: Event 1
V3: Event 2
V4: Event 3
Remainder of the column is empty

If I input

Please Login or Register  to view this content.

I get 4.

It originally looked liek it was working when the name manager highlighted the area but now I keep getting too many cells returned

Any help would be appreciated.

Thanks again,
smls

[FDibbins]

Remainder of the column is empty

Are you sure its empty?
Try the =counta(V:V) on its own and see what it gives you

[FDibbins]

hmm wait, in your offset...
OFFSET(EventAnchor,COUNTA(DropDowns!V:V),,,)
you are using COUNTA() to determine the starting point or the offset?

[smls]

Hey FDibbins,

I did, with just =counta(V:V) I am getting 4.

I can't figure out why I am getting 4 there but getting 2:21 back in my named range list.

I am probably missing something obvious but it's driving me crazy

[smls]

I was trying to use the counta to determine the row height to offset. The idea was the get the range by starting in V1 then offsetting from V1 to the last non blank cell in col V.

[FDibbins]

Why not just have the whole range determined by the OFFSET?

Perhaps adapt 1 of these...
1:Expand Down as Many Rows as There are Numeric Entries.
*** In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)
2:Expand Down as Many Rows as There are Numeric and Text Entries.
*** In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

You could use DropDowns!$V$2 as the start OF THE offset...
=offset(DropDowns!$V$2,..............................

[smls]

Wow, there we go, I was making it was too complicated apparently. That worked and makes more sense. I guess I was trying to make it work the other way and just couldn't figure it out so I got stuck on it.

Much appreciated.

[FDibbins]

Often happens Glad to help and thanks for the feedback