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 header:(Last 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.

What you've described is normal operation of a drop down created using Data
Validation where there are empty cells in the list that provides the contents
of the drop down.

I'm guessing that somewhere you have given the list of names on the first
sheet a name (named range) and it is defined to refer to Sheet1!$B$5:$B$300

What you could do is either somehow provide something to show up in the
as-yet unused cells in that range, but since I don't know what's in them now
(a formula, I suspect) it's difficult to tell you how to do that without
breaking what you have set up.

An alternative is to kind of redefine the range that defines the named
range. You could do this with code like this attached to the
Worksheet_Activate() event of any sheets where you are going to set up the
drop down to refer back to that list. This assumes that the name you've
given the list is MyNamesRange, and that it is on a sheet named Sheet1 -
change the code to reflect your reality:

Private Sub Worksheet_Activate()
ActiveWorkbook.Names("MyNamesList").RefersTo = _
"='Sheet1'!$B$5:" & Worksheets("Sheet1"). _
Range("B" & Rows.Count).End(xlUp).Address
End Sub

Hope this helps, or at least gives you some ideas on an approach to curing
the situation.

"Anita" wrote:

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 header:(Last 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.

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
What you've described is normal operation of a drop down created using Data
Validation where there are empty cells in the list that provides the contents
of the drop down.

I'm guessing that somewhere you have given the list of names on the first
sheet a name (named range) and it is defined to refer to Sheet1!$B$5:$B$300

What you could do is either somehow provide something to show up in the
as-yet unused cells in that range, but since I don't know what's in them now
(a formula, I suspect) it's difficult to tell you how to do that without
breaking what you have set up.

An alternative is to kind of redefine the range that defines the named
range. You could do this with code like this attached to the
Worksheet_Activate() event of any sheets where you are going to set up the
drop down to refer back to that list. This assumes that the name you've
given the list is MyNamesRange, and that it is on a sheet named Sheet1 -
change the code to reflect your reality:

Private Sub Worksheet_Activate()
ActiveWorkbook.Names("MyNamesList").RefersTo = _
"='Sheet1'!$B$5:" & Worksheets("Sheet1"). _
Range("B" & Rows.Count).End(xlUp).Address
End Sub

Hope this helps, or at least gives you some ideas on an approach to curing
the situation.

"Anita" wrote:

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 header:(Last 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.

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.

"Anita" wrote:

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
What you've described is normal operation of a drop down created using Data
Validation where there are empty cells in the list that provides the contents
of the drop down.

I'm guessing that somewhere you have given the list of names on the first
sheet a name (named range) and it is defined to refer to Sheet1!$B$5:$B$300

What you could do is either somehow provide something to show up in the
as-yet unused cells in that range, but since I don't know what's in them now
(a formula, I suspect) it's difficult to tell you how to do that without
breaking what you have set up.

An alternative is to kind of redefine the range that defines the named
range. You could do this with code like this attached to the
Worksheet_Activate() event of any sheets where you are going to set up the
drop down to refer back to that list. This assumes that the name you've
given the list is MyNamesRange, and that it is on a sheet named Sheet1 -
change the code to reflect your reality:

Private Sub Worksheet_Activate()
ActiveWorkbook.Names("MyNamesList").RefersTo = _
"='Sheet1'!$B$5:" & Worksheets("Sheet1"). _
Range("B" & Rows.Count).End(xlUp).Address
End Sub

Hope this helps, or at least gives you some ideas on an approach to curing
the situation.

"Anita" wrote:

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 header:(Last 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.

I am sorry to be so dumb but I still can't get it.

My data validation is located on "labor 1"

my source list is named "laborer" and the sheet itself is named "Labor Data
Sheet"

The names that are entered on the "labor data sheet" are manually entered on
that sheet in column b starting at row 5.

Would it be possible to e-mail you the file so it would be easier to
understand? I am probably not doing a good job of explaining.

"JLatham" wrote:

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.

"Anita" wrote:

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
What you've described is normal operation of a drop down created using Data
Validation where there are empty cells in the list that provides the contents
of the drop down.

I'm guessing that somewhere you have given the list of names on the first
sheet a name (named range) and it is defined to refer to Sheet1!$B$5:$B$300

What you could do is either somehow provide something to show up in the
as-yet unused cells in that range, but since I don't know what's in them now
(a formula, I suspect) it's difficult to tell you how to do that without
breaking what you have set up.

An alternative is to kind of redefine the range that defines the named
range. You could do this with code like this attached to the
Worksheet_Activate() event of any sheets where you are going to set up the
drop down to refer back to that list. This assumes that the name you've
given the list is MyNamesRange, and that it is on a sheet named Sheet1 -
change the code to reflect your reality:

Private Sub Worksheet_Activate()
ActiveWorkbook.Names("MyNamesList").RefersTo = _
"='Sheet1'!$B$5:" & Worksheets("Sheet1"). _
Range("B" & Rows.Count).End(xlUp).Address
End Sub

Hope this helps, or at least gives you some ideas on an approach to curing
the situation.

"Anita" wrote:

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 header:(Last 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.

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

"Anita" wrote:

I am sorry to be so dumb but I still can't get it.

My data validation is located on "labor 1"

my source list is named "laborer" and the sheet itself is named "Labor Data
Sheet"

The names that are entered on the "labor data sheet" are manually entered on
that sheet in column b starting at row 5.

Would it be possible to e-mail you the file so it would be easier to
understand? I am probably not doing a good job of explaining.

"JLatham" wrote:

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.

"Anita" wrote:

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
What you've described is normal operation of a drop down created using Data
Validation where there are empty cells in the list that provides the contents
of the drop down.

I'm guessing that somewhere you have given the list of names on the first
sheet a name (named range) and it is defined to refer to Sheet1!$B$5:$B$300

What you could do is either somehow provide something to show up in the
as-yet unused cells in that range, but since I don't know what's in them now
(a formula, I suspect) it's difficult to tell you how to do that without
breaking what you have set up.

An alternative is to kind of redefine the range that defines the named
range. You could do this with code like this attached to the
Worksheet_Activate() event of any sheets where you are going to set up the
drop down to refer back to that list. This assumes that the name you've
given the list is MyNamesRange, and that it is on a sheet named Sheet1 -
change the code to reflect your reality:

Private Sub Worksheet_Activate()
ActiveWorkbook.Names("MyNamesList").RefersTo = _
"='Sheet1'!$B$5:" & Worksheets("Sheet1"). _
Range("B" & Rows.Count).End(xlUp).Address
End Sub

Hope this helps, or at least gives you some ideas on an approach to curing
the situation.

"Anita" wrote:

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 header:(Last 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.