How to have a the liist items appear at the top, not bottom

S

Smith@ Clark

I have set up various lists in a spreadsheet. When I click on the drop
down, I start at the bottom, where it is blank, rather than at the top of the
list. How do I make the drop down begin at the top of the list?

Thanks
 
A

Anne Troy

(Of course, I can't reproduce this behavior NOW when I want to test it!!)
When you select the cells from which to validate, select only populated
cells. This is one of my pet peeves about data validation. The other thing
you could do, which is what I do: Make your first selection something like
"Pick one". Then conditionally format the cell so that if it contains "pick
one", it's got a yellow background. This way you can choose the "pick one"
and leave it chosen as the default status, and also emphasize what still
needs to be done by the user.
************
Anne Troy
www.OfficeArticles.com
 
D

DOR

The reason the bottom of the list displays is that there is probably
one or more a blank cells at the bottom of the list, and the
drop-down's behavior is to display the list such that the entry that
corresponds to the current contents of the entry cell is highlighted.
You can cause the top of the list to display in either of two ways, in
addition to Anne's approach, which requires you to prepopulate the
entry cells up front.

1. Make the first cell of all lists blank - not very elegant, and
possibly a problem for other uses of the list, but easy.

2. Define each list as a dynamic list - using OFFSET

Assume the list is in column A and nothing else is in column A. Enter
the following formula in the Source field of the DataValidation dialog
box:

=OFFSET(A1,0,0,COUNTA(A:A))

If the list has a header in row 1, then

=OFFSET(A1,1,0,COUNTA(A:A)-1)

If the column containing the list also contains other data, and list
occupies a contiguous subset of the column, starting at the top of a
range called ListRange, (ListRange is longer than the list)

=OFFSET(ListRange,0,0,COUNTA(ListRange))

If ListRange includes the header and you don't want it in the drop-down

=OFFSET(ListRange,1,0,COUNTA(ListRange)-1)


HTH
 
Top