How can I use a named range for "pick from drop-down list"?

[Guest]

When the (right-click) "Pick From Drop-Down List..." command is used in Excel
to facilitate key-entry, it only works for contiguous entries in cells in the
column above the input cell. blank cells in between break the link.

Is there a way to designate a named range of terms as the basis for the
"drop-down" list?

Microsoft,this should be a standard option!

 

[Don Guillett]

You can use data validation>make a list >name it >in the
data>validation>select list >=mylist

 

[Gord Dibben]

No, but the same thing can be done using a Data Validation Drop-Down pick
list.

See Debra Dalgleish's site for more info and methods.

http://www.contextures.on.ca/xlDataVal01.html


Gord Dibben Excel MVP

 

[Guest]

This creates a validation drop-down list, but if you begin typing in the
cell, entries don't "auto-complete" based on the characters you've entered.
If you right-click on a cell and select "Pick From Drop-Down List..." and
start typing, Excel will suggest entries based on characters you've typed
that match contiguous cells in the column above the target cell.

If there is a blank cell in between, the command doesn't pick anything up.
If I could point to a named range rather than depending on entries in cells
above the target cell, the "Pick From Drop-Down List" command would be much
more usable.

 

[Guest]

Gord,

I can't find a way to create the "auto-suggestion" that "Pick From Drop-Down
List..." provides using Data Validation Drop-Down information from Debra's
site. You mention a Drop-Down pick list using Data Validation, which sounds
promising, how do I invoke this?

Thanks!

Doug

 

[Debra Dalgleish]

Data Validation dropdown lists don't support autocomplete. If you can
use programming, there are instructions here for adding a combobox from
which you can select one of the values from the data validation list. In
the combobox, you can enable autocomplete:

http://www.contextures.com/xlDataVal11.html

 

[Guest]

Debra,

THANKS! I'll try this. Hopefully, the next release of Excel will include
this as a standard, or optional feature to the pick from drop-down command.

Thanks to all of you for your quick and considered responses.

Doug

 

[Guest]

Debra,

Your Combo Box code worked, but the nested boxes and click-through
requirements to load results to the cell were a bit confusing. I found out
that I could copy/paste the named ranges into rows above my validation
dropdown cells and engage "Pick from drop down list", and the cells would
auto complete. I just hid the rows above (not very elegant, but servicable.

Thanks for your help!

Doug