Excel Drop down List

June 5, 2009 at 07:53:07
Specs: Windows XP
I have a drop down list created and set it to include a range of cells so I can add item's to it at a future date. My problem is when I select the list to add something from the list, it shows the next blank box for an entry and I have to use the up arrow to see my entries. Is there a way to have the list start at the top entry then I can scroll down to the entry I want?

See More: Excel Drop down List

Report •

June 5, 2009 at 08:05:14
I'm a bit confused.

If I set a Data Validation List range as A1:A17, but only populate A1:A10, I will see 10 items in my drop down.

If I add an item to A11, I will then see 11 items. The only way I get a blank is if I leave, let's say A11, empty and populate A1:A10 and A12.

What am I missing in your question?

Report •

June 5, 2009 at 08:32:37
Lets say I have A1:A118, I'm using A1-A12, when I click the drop down list at the top shows A13 on down and I have to click the up arrow to see A1-A12.

Report •

June 5, 2009 at 08:59:18
OK, I think I was able to duplicate your issue, but it doesn't seem to be consistant. (See below for a possible solution)

I'm not sure, but it appears that it is dependent on where your cursor is what you click the drop down arrow for the first time.

As you know, the drop down highlights the item in the list that was last selected and appears in the cell. If you happen to be in a blank cell when you click the arrow (maybe just for the first time?) then the drop down selects that entry, adds it to your list and that's the item it highlights the next time you click the arrow.


However, there is a much better way to create a drop down list of varying length:

Use a Dynamic Named Range that changes it's size depending on how many entries are in the range.

For example,

Go to Insert...Name...Define and enter a range name, such as myRange.

In the "Refers" to box enter this:


This will ceate a Named range that will start at A1 and extend down column A as far as there is data at any given time.

In your Data Validation Source field, use =myRange

Report •

Related Solutions

Ask Question