Drop Down List Format

[jswalsh33]

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

 

[Gord Dibben]

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

 

[jswalsh33]

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