Drop Down List Format
 

I have worksheets with a number of drop down lists created with data
validation. I would like the operator to be able to edit these lists, so I
have put in a number of blank cells at the bottom of each list. When the list
is selected the scroll goes to the bottom of the list (blank cells). I have
checked the ignore blank cells box on the validation, but that does not fix
the problem.

Is the a way to get the drop down list scroll to start at the top?

Thanks for your help.

Jim

Drop Down List Format
 

You misunderstand the purpose of "ignore blanks"

Blank Cells in Source List

If the source list is a named range that contains blank cells, users may be
able to type any entry, without receiving an error message.

I think what you really want is a dynamic named range that will show only
non-blank cells in the range and expand as you add more items.

See Debra Dalgleish's site for dynamic range setup.

http://www.contextures.on.ca/xlDataVal08.html#Dynamic

Or this site for a flexible DV list sample workbook from Ron Codere.

http://www.contextures.on.ca/excelfiles.html#DV0038


Gord Dibben MS Excel MVP

On Mon, 22 Jun 2009 16:02:01 -0700, jswalsh33
wrote:

I have worksheets with a number of drop down lists created with data
validation. I would like the operator to be able to edit these lists, so I
have put in a number of blank cells at the bottom of each list. When the list
is selected the scroll goes to the bottom of the list (blank cells). I have
checked the ignore blank cells box on the validation, but that does not fix
the problem.

Is the a way to get the drop down list scroll to start at the top?

Thanks for your help.

Jim

Drop Down List Format
 

Gord,

Thank-you very much. That worked very well.

Incidentally, when I followed the procedure from Contextures a blank cell
was inserted at the bottom of the list and when the list was selected the
scroll bar scrolled to the bottom of the list.

I found that if a put a blank cell at the top of the list, the scroll bar
came up at the top of the list.

Jim

"Gord Dibben" wrote:

You misunderstand the purpose of "ignore blanks"

Blank Cells in Source List

If the source list is a named range that contains blank cells, users may be
able to type any entry, without receiving an error message.

I think what you really want is a dynamic named range that will show only
non-blank cells in the range and expand as you add more items.

See Debra Dalgleish's site for dynamic range setup.

http://www.contextures.on.ca/xlDataVal08.html#Dynamic

Or this site for a flexible DV list sample workbook from Ron Codere.

http://www.contextures.on.ca/excelfiles.html#DV0038


Gord Dibben MS Excel MVP

On Mon, 22 Jun 2009 16:02:01 -0700, jswalsh33
wrote:

I have worksheets with a number of drop down lists created with data
validation. I would like the operator to be able to edit these lists, so I
have put in a number of blank cells at the bottom of each list. When the list
is selected the scroll goes to the bottom of the list (blank cells). I have
checked the ignore blank cells box on the validation, but that does not fix
the problem.

Is the a way to get the drop down list scroll to start at the top?

Thanks for your help.

Jim