Pick from drop-down list gives empty or erroneous result

[rumplestiltskin]

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

 

[Clif McIrvin]

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.)

 

[rumplestiltskin]

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

 

[Clif McIrvin]

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.)

 

[Gord Dibben]

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

 

[Mike]

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




- 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

 

[Gord Dibben]

Interesting point Mike.

Never noticed that.


Gord