To make data entry easier, you can create a drop down list of items in a worksheet cell. Then, instead of typing a product name in an order list, you can select a valid product name from the list.
However, in some worksheets, when you click the arrow to open a drop down list, the selection goes to a blank at the bottom of the list, instead of the first item in the list. That can be annoying if you have to scroll back to the top of a long list, where you have the put the most popular items.
Why It Happens
Why does this happen, and how can you prevent it?
In the example shown above, the drop down list is based on a range named Products. The person who set up the list left a few blank cells at the end, where new items could be added.
This might seem like a good idea when you're setting up a list, but it causes problems.
When the drop down list opens, it tries to match whatever is currently in the cell. In cell D3, Pencils was previously selected, and the list opens to Pencils.
If there's a blank cell in the source list, and the cell with the data validation list is blank, the list will open with the matching blank entry selected.
Prevent the Problem
To prevent this problem, you can remove the blanks from the source list, by using a dynamic range, which will adjust automatically when items are added or removed.
In this example, the OFFSET formula is:
=OFFSET(Prices!$B$2,0,0,COUNTA(Prices!$B:$B)-1,1)
It counts the items in column B of the Prices sheet, where the Products are listed. The heading, in cell B1, is not included in the list.
Once the dynamic range is created, the blanks are not included in the Products list, and the drop down works correctly.
Download the Sample File
To download the sample file with a dynamic range, please visit the Data Validation Tips & Quirks page, at this link: Drop Down List Opens with Blank Selected
Watch the Dynamic Range Video
To see the steps for setting up a dynamic named range for the product list, please watch this short video tutorial.
______________________
Debra:
This will make the list no longer editable in the cell will it? I have the problem that you mention with blank selection in the list, but I need the selection to be editable. Is there any way to deal with this?
Thanks
@Stan, you could change the Error Alert setting in the data validation cell, so it won't show an error message. There are instructions here:
http://www.contextures.com/xlDataVal08.html#Invalid
If you turn your source list into a table, this fomula used in the Data Validation list field will automatically expand with additional entries in the table.
=INDIRECT("Table1[Product]")
David
I liked the idea of using a Table as the source for a list ... but Tables don't seem to like Array Formulas ... which I use to create some of my List Data .... any alternative solutions?
Cheers
James
Good Advice