dynamic range in combo box

[Ziggy943]

I want to use a dynamic range in a combo box. The formula seems to work fine. When I inspect the range by clicking on the formula field in the 'name manager', I can see the field expand or contract, as expected, when I add or delete data.. I am using =OFFSET(Applicants!$A$1,1,2,COUNTA(Applicants!$A:$A)-1,1). The formula appears to be doing its job.

I typed the name into the ListFillRange field of the combobox. When I add names into the field I now lose the bottom of the range in the display of the combobox. In other words, once in the combobox, the dispalyed field is not dynamic. But the field in the data page expands just like you would expect.

Is there a solution. Thanks for your help.

[gjlindn]

Hi Ziggy...could you upload a sample workbook?

[Ziggy943]

I am new to this. Hope it works.

The names and dynamic formula is on the "Applicants" tab. The range can be inspected with the name manager.

The problem shows up in the drop-down on the 2nd tab. Note that when you add a name it drops the visibility of one at the bottom.

Thanks for the help.

[Ziggy943]

Forgot to mention.

I add names in the first tab. Only first and last need to be filled in.

[gjlindn]

Wow that's a crazy problem The only way I could get it to refresh was through a change event on the Applicants tab. I had to add the following code to the worksheet object in the VBE. Let me know if you're not familiar with how to do that.

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("2. 2012 New Students").ComboBox1.ListFillRange = "Applicants"
End Sub

[Ziggy943]

I do not know how to do that.

Will that auto-update to ListFillRange? I thought the dynamic range would automatically expand in the dropdown.

Thank you for the help.

[Ziggy943]

I put the line into the "Applicants" macro and it seems to work. Thank you so very much.

Ziggy

[gjlindn]

Hi Ziggy...glad it's working for you. If you think it's good enough to not look for any other answers, feel free to mark this thread as solved. Also...would really help me out if you clicked my scales

[Ziggy943]

Again, Thanks. That works.

I was looking for the solved button but I don't see it. Can you guide me through that and the scales?

[gjlindn]

Hi Ziggy. Rule number 9 on the forum rules explains how to mark a thread as solved. The scales are on the right side of each post. Have a great day!

http://www.excelforum.com/forum-rule...rum-rules.html