Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
I know how to use Data Validation to create a drop-down list in a
cell. However, there's a contextual menu command "Pick from drop-down list..." that shows nothing except a blank choice. If I enter something in the cell above it, then that item -does- appear in the drop-down list. (Enter "1" in cell A1 and you can pick "1" from the drop-down list that appears when I right-click in cell A2 and select "pick from drop-down list". However, if I enter, let's say, 1, 2, 3, 4, and 5 in cells A1-A5 and then right-click in cell A6 to select "pick from drop-down list", I see only one -blank- choice. It's obvious this contextual menu item has nothing to do with Data Validation (as that creates its own menu arrows to the right of the cell) and selecting the "pick from drop-down list" must refer to some other option I'm just not grasping. Thanks for any assistance. Barry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
"rumplestiltskin" wrote in message
... I know how to use Data Validation to create a drop-down list in a cell. However, there's a contextual menu command "Pick from drop-down list..." that shows nothing except a blank choice. If I enter something in the cell above it, then that item -does- appear in the drop-down list. (Enter "1" in cell A1 and you can pick "1" from the drop-down list that appears when I right-click in cell A2 and select "pick from drop-down list". However, if I enter, let's say, 1, 2, 3, 4, and 5 in cells A1-A5 and then right-click in cell A6 to select "pick from drop-down list", I see only one -blank- choice. It's obvious this contextual menu item has nothing to do with Data Validation (as that creates its own menu arrows to the right of the cell) and selecting the "pick from drop-down list" must refer to some other option I'm just not grasping. Thanks for any assistance. Barry You got my curiosity up. xl2010. I didn't find anything in the on-board (installed, not on-line) help. A few minutes of experimentation revealed that the drop-down list is only available when two conditions are both true: 1) the cell immediately above the active cell is not empty, and 2) the column containing the active cell contains at least some text values And, the drop-down list contains *only* the text values from the column. Good question, Barry. I'd sure like to see responses from others who know more about this feature. -- Clif McIrvin Change nomail.afraid.org to gmail.com to reply by email. (nomail.afraid.org has been set up specifically for use in usenet. Feel free to use it yourself.) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
A-ha! Clarification! Thank you, Clif. When I used text in A1-A5 and
then selected "pick from drop-down list" in the contextual menu when A6 was the selected cell, then I DID see the choices. Apparently it has to be text and not numbers. If one of the cells contains a number, that number is not included in the resulting list. Example: A1: one A2: two A3: 3 A4: four A5: five Control click in A6 and select "pick from drop-down list" and you'll see a list containing "one, two, four, five" but no "3" as it's a number. The Data Validation rules regarding lists are much more useful. I'll pass this along to my students (as we were wondering about this in class today!). Thanks again, Barry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
"rumplestiltskin" wrote in message
... A-ha! Clarification! Thank you, Clif. When I used text in A1-A5 and then selected "pick from drop-down list" in the contextual menu when A6 was the selected cell, then I DID see the choices. Apparently it has to be text and not numbers. If one of the cells contains a number, that number is not included in the resulting list. Example: A1: one A2: two A3: 3 A4: four A5: five Control click in A6 and select "pick from drop-down list" and you'll see a list containing "one, two, four, five" but no "3" as it's a number. The Data Validation rules regarding lists are much more useful. I'll pass this along to my students (as we were wondering about this in class today!). Thanks again, Barry I take this to be a user interface enhancement of the "data auto-complete" (whatever it's name was) that I got used to in xl2003 ... I think that was always "better behaved" with text data, too. -- Clif McIrvin Change nomail.afraid.org to gmail.com to reply by email. (nomail.afraid.org has been set up specifically for use in usenet. Feel free to use it yourself.) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
Cliff
Right-click "Pick from list" has been around in all versions since at least Excel 97 Works for text only. Must have filled contiguous cells above or below selected cell. Gord Dibben MS Excel MVP On Tue, 19 Oct 2010 18:05:45 -0500, "Clif McIrvin" wrote: "rumplestiltskin" wrote in message ... A-ha! Clarification! Thank you, Clif. When I used text in A1-A5 and then selected "pick from drop-down list" in the contextual menu when A6 was the selected cell, then I DID see the choices. Apparently it has to be text and not numbers. If one of the cells contains a number, that number is not included in the resulting list. Example: A1: one A2: two A3: 3 A4: four A5: five Control click in A6 and select "pick from drop-down list" and you'll see a list containing "one, two, four, five" but no "3" as it's a number. The Data Validation rules regarding lists are much more useful. I'll pass this along to my students (as we were wondering about this in class today!). Thanks again, Barry I take this to be a user interface enhancement of the "data auto-complete" (whatever it's name was) that I got used to in xl2003 ... I think that was always "better behaved" with text data, too. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
On Oct 20, 4:09*am, Gord Dibben wrote:
Cliff Right-click "Pick from list" has been around in all versions since at least Excel 97 Works for text only. Must have filled contiguous cells above or below selected cell. Gord Dibben * * MS Excel MVP On Tue, 19 Oct 2010 18:05:45 -0500, "Clif McIrvin" wrote: "rumplestiltskin" wrote in message .... A-ha! Clarification! Thank you, Clif. When I used text in A1-A5 and then selected "pick from drop-down list" in the contextual menu when A6 was the selected cell, then I DID see the choices. Apparently it has to be text and not numbers. If one of the cells contains a number, that number is not included in the resulting list. Example: A1: one A2: two A3: 3 A4: four A5: five Control click in A6 and select "pick from drop-down list" and you'll see a list containing "one, two, four, five" but no "3" as it's a number. The Data Validation rules regarding lists are much more useful. I'll pass this along to my students (as we were wondering about this in class today!). Thanks again, Barry I take this to be a user interface enhancement of the "data auto-complete" (whatever it's name was) that I got used to in xl2003 ... I think that was always "better behaved" with text data, too.- Hide quoted text - - Show quoted text - In Excel 2003, if the original numeric entry is preceded by a single quote mark, it will be available form the "Pick from list" and, if selected, will be entered as a number in the cell. Mike Forrest |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick from drop-down list gives empty or erroneous result
Interesting point Mike.
Never noticed that. Gord On Mon, 1 Nov 2010 04:33:39 -0700 (PDT), Mike wrote: In Excel 2003, if the original numeric entry is preceded by a single quote mark, it will be available form the "Pick from list" and, if selected, will be entered as a number in the cell. Mike Forrest |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down list based on other drop down list pick | Excel Discussion (Misc queries) | |||
Pick From Drop-Down List... | Excel Worksheet Functions | |||
Drop Down Pick List | Excel Discussion (Misc queries) | |||
how do i get a drop down pick list in a cell | Excel Worksheet Functions | |||
Pick from a drop-down list | Excel Worksheet Functions |