|
How can I use a named range for "pick from drop-down list"?
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! |
You can use data validationmake a list name it in the
datavalidationselect list =mylist -- Don Guillett SalesAid Software "InstantZen" wrote in message ... 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! |
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 On Fri, 13 May 2005 10:41:03 -0700, "InstantZen" wrote: 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! |
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. "Don Guillett" wrote: You can use data validationmake a list name it in the datavalidationselect list =mylist -- Don Guillett SalesAid Software "InstantZen" wrote in message ... 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! |
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 "Gord Dibben" wrote: 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 On Fri, 13 May 2005 10:41:03 -0700, "InstantZen" wrote: 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! |
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 Doug Evans (InstantZen) wrote: 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 "Gord Dibben" wrote: 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 On Fri, 13 May 2005 10:41:03 -0700, "InstantZen" wrote: 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! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
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 "Debra Dalgleish" wrote: 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 Doug Evans (InstantZen) wrote: 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 "Gord Dibben" wrote: 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 On Fri, 13 May 2005 10:41:03 -0700, "InstantZen" wrote: 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! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
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 "Doug Evans (InstantZen)" wrote: 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 "Debra Dalgleish" wrote: 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 Doug Evans (InstantZen) wrote: 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 "Gord Dibben" wrote: 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 On Fri, 13 May 2005 10:41:03 -0700, "InstantZen" wrote: 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! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 08:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com