Default to Top of Drop Down List

[Anita]

I need help. I have a drop down list which I have been able to set up to
automatically update and everything else that should be. I CANNOT get it set
up to default to the top line in the list.

Cell B4 is the headerLast Name, First Name)

All of the cells from A5 to A120 are numbered with 1, 2, 3, 4, etc.

Cells B5 to B300 (to automatically update) are blank until a new file is
created listing employees names:

Smith, John
Brown, Jim
Gray, Steve

When I open the drop down in a differnt worksheet in the workbook it opens
to the cell immediately below 'Gray, Steve' ad I have to manually scroll to
the top to select 'Smith, John'.

How do I get it to open on "Smith, John".

I appreciate your help.

 

[Anita]

There are no formulas in the drop down source. The only data on my sheet
from which i do the data validation is the header row on A4.

If I would use the following info how would I write the formula based on the
info I have:

Row B is named 'myrange' and covers the area from B5 to B300 located on
Labor Data Sheet in my workbook and the drop down list is in Labor 1 sheet
down arrow located in B14.

Thank you so much for your assistance.

Anita

 

[JLatham]

Going back to your first post, it says " When I open the drop down in a
differnt worksheet in the workbook ..." I was assuming you're using data
validation in a cell on that different worksheet to display the list to
choose from. Or you may actually be using a drop down control. In either
case I am also assuming that the source for the information is the named
range "MyRange" which you have said covers the range from B5 to B300 on sheet
'Labor Data Sheet'.

The code I provided would go into the different worksheet's code segment,
and you would change "MyNamesList" in that code to "MyRange" and change
'Sheet1' to 'Labor Data Sheet'.

To get to the proper area to put the code into, right-click on the different
sheet's tab and choose View Code from the popup list. That will open the VB
Editor and you can put the code into the sheet's code segment there. You
should be able to cut and paste from my other post and then simply edit the
sheet name in the two places it appears in it, and the name of the range in
the one place it appears and it should work for you.

What will happen is that when you choose (activate) that different sheet,
the definition of MyRange will be changed to refer to the range starting at
B5 and continuing down to the first empty row in the list - it assumes that
there is at least one entry in that range at B5 and that you don't have other
information in column B below row 300.

You say there are no formulas in the drop down source (B5:B300 on the Labor
Data Sheet), and yet you said "Cells B5 to B300 (to automatically update) are
blank until a new file is created listing employees names..." so I assumed
that by "to automatically update" you meant that there was some formula in
them to automatically pick up the new names from some place and put them
there, or perhaps that code did it. I was kind of concerned that by typing
some dashes or other characters into those cells that are now empty (from B8
down to B300) you would destroy any formula that was in them or confuse code
that might be looking for an empty cell to use for the next name. If that's
not the case, you could simply type some 'filler' characters into the first
empty cell and then fill that down to B300 to get rid of the empty cell that
is causing the drop down on the different sheet to jump to the bottom instead
of defaulting to the top of the list.

Or maybe I don't yet have a clear picture of your set up right now.

 

[JLatham]

Sure, go ahead and send it to (remove spaces) HelpFrom @ jlathamsite.com and
it'll get to me.