Data Validation Drop Down List Display

**laundry125** · 07-27-2008, 10:27 PM

I have created several drop down lists through Data Validation. When the down arrow is selected, and the list is displayed, how do I get the first choice in the list to be highlighted rather than something further down at the end of the list?
*Note - I have blank spaces at the end of my lists so new items can be added to the list as needed.

Thank You.

**Paul** · 07-27-2008, 10:51 PM

Hi Laundry,

With Data Validation, the currently selected item from the drop-down list will be the highlited item when you click the drop-down arrow. If nothing has been selected, the first item should be highlited.

As for adding spaces for future expansion.. why? You can use a dynamic length list to handle that, as follows:

Let's say you have list of names in A1:A5 (Bob, Tom, Joe, Tim, Ted).
1. Click Insert -> Name -> Define..
2. Beneath 'Names in workbook', give your Name a new, well, name, like "MemberList" (without the quotes)
3. In the 'Refers to:' box, type:
=OFFSET($A$1,0,0,COUNTA(A:A),1)
4. Click Add, then OK

In the cell you want your drop-down list, click Data -> Validation, then set 'Allow' to "List", and 'Source' to "=MemberList" (without the quotes). Click OK.

At that point, you would see the list of names from A1:A5 in your drop-down list. Next add the name Paul in A6. Click your drop-down arrow again and you'll see Paul added to the bottom of the list.

Tada!

**laundry125** · 07-27-2008, 10:55 PM

Thank you. After sending this post, I did some research and realized I should do exactly what you suggested. I will try it.

Thanks again.

**laundry125** · 07-28-2008, 12:06 AM

Hi again.

I created a dynamic length list but when the cell is blank and I click the down arrow to display the drop down list, it still does not highlight the start of the list, it highlights a blank space at the bottom. What am I missing?

Thanks.

**Paul** · 07-28-2008, 12:21 AM

Are you sure the cells below your list are actually blank and don't contain spaces or other non-viewable characters?

Or was your list created using formulas, and it's leaving cells with "" at the bottom of the list? In that case, change the formula for the Name you defined to

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

not COUNTA.

You could also try deleting all of the "blank" cells below your list in the column. (Select the cells, click Edit -> Delete -> Shift Cells Up)

**laundry125** · 07-28-2008, 12:52 AM

I will try this, and how does this work with dependant drop down lists?

**Paul** · 07-28-2008, 01:14 AM

It works just fine. Rather than try to explain here, they've done a good job at this site:

http://www.contextures.com/xlDataVal02.html

**laundry125** · 07-28-2008, 01:20 AM

Thank you again. I thought I had read everything there was about Data alidation on Contextures.com, but I guess not. I love that website almost as much as I do this one!

Thanks. I'll read the contextures artcile and give it a try.