Hello, a very basic question about Excel 2003.

Does Excel have a method of letting you use a drop-down menu to select
data that's already been entered in a column?

I'd like to streamline my selecting a piece of information, and I
figured a drop-down menu might be the simplest way. I know Excel has
an auto-complete feature, but if a drop-down menu, based on data
already entered in a column, can be created, I'd prefer that.

If this is possible, without macros or VBA code, or anything like
that, what's the procedure?

If it's not possible, that's all right. Thank you!

Jd

Does Excel have a method of letting you use a drop-down menu to select
data that's already been entered in a column?

One thought would be the Autofilter functionality
Applied via selecting the col, then clicking Data Filter Autofilter
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

In a column, right-click and "Pick from drop down list" may help/


Gord Dibben MS Excel MVP

On Wed, 02 Apr 2008 09:14:37 -0400, T. Hulot wrote:

Hello, a very basic question about Excel 2003.

Does Excel have a method of letting you use a drop-down menu to select
data that's already been entered in a column?

I'd like to streamline my selecting a piece of information, and I
figured a drop-down menu might be the simplest way. I know Excel has
an auto-complete feature, but if a drop-down menu, based on data
already entered in a column, can be created, I'd prefer that.

If this is possible, without macros or VBA code, or anything like
that, what's the procedure?

If it's not possible, that's all right. Thank you!

Jd

This is exactly what I want, but is there a way to make this a default
setting for an entire column? Or will I have to right-click and
select this for each and every cell in the column?

In a column, right-click and "Pick from drop down list" may help/

You can use the 'Data Validation' menu command:
First you would need to create your list somewhere out of the way of your
work area eg cell AA2 has HOUSE, cell AA3 has FLAT, cell AA4 has COTTAGE,
cell AA5 has GARAGE, etc
Now highlight the column or range of cells in a column that you want the
drop-down list to be used in.
Then use the Data Validation menu command and in SettingsAllow choose List
and this opens up Source - enter your list range here eg AA2:AA5
Click on OK and that should do it for you.

"T. Hulot" wrote:

This is exactly what I want, but is there a way to make this a default
setting for an entire column? Or will I have to right-click and
select this for each and every cell in the column?

In a column, right-click and "Pick from drop down list" may help/

This worked flawlessly.
It's EXACTLY what I want.
Thank you.

You can use the 'Data Validation' menu command:
First you would need to create your list somewhere out of the way of your
work area eg cell AA2 has HOUSE, cell AA3 has FLAT, cell AA4 has COTTAGE,
cell AA5 has GARAGE, etc
Now highlight the column or range of cells in a column that you want the
drop-down list to be used in.
Then use the Data Validation menu command and in SettingsAllow choose List
and this opens up Source - enter your list range here eg AA2:AA5
Click on OK and that should do it for you.

Not quite sure what you are asking.

Right-click on any cell you want to enter data into.

Pick from list appears to work in non-contguous cells above the list items but
not in non-contiguous cells below the list items.

Gord

On Wed, 02 Apr 2008 10:46:40 -0400, T. Hulot wrote:

This is exactly what I want, but is there a way to make this a default
setting for an entire column? Or will I have to right-click and
select this for each and every cell in the column?

In a column, right-click and "Pick from drop down list" may help/

Not quite sure what you are asking.




Let me elaborate on what I'm doing. It's extremely simple and basic.

I've created two databases with Access. One is flat-file, one is
relational.

They are movie collection databases. Nothing fancy.

But I want (for various reasons) a stripped-down list of my movies in
spreadsheet form.

The only three columns in the spreadsheet will be:

Title
Genre
Location

There will be only 20 genre items, i.e. drama, comedy, foreign,
mystery, etc.

I want some semblance of consistent data entry. For example, I don't
want to enter sience fction [sic] by accident.

Furthermore, there will be only eight Location items.

I do *not* like auto-complete, so I was looking for a way to
streamline the Genre and Location selection process. I figured a
drop-down menu would be the easiest way.

In Access, there is a feature called a Lookup Table. It's a table
with pre-entered items, with which you can build a drop-down menu,
called a Combo Box. This ensures consistent data entry, and also
makes it easier to add new items to the list. You simply add new
things to the Lookup Table.

Basically, I want the same thing in Excel, if possible. Apparently,
it is.

However, I want to configure an entire column so that each cell in
that column will have a drop-down menu, rather than right-click each
and every cell.

From what I've read in this thread, something similar to a Lookup
table is possible, and having a drop-down menu in each cell of a
column is possible. I just have to try it, which I will do soon.

That's basically all I want. I hope this clarifies things further.

What about

ToolsOptionsEdit tab, check "Enable AutoComplete for cell values"

?

HTH,
JP


On Apr 2, 10:46*am, T. Hulot wrote:
This is exactly what I want, but is there a way to make this a default
setting for an entire column? *Or will I have to right-click and
select this for each and every cell in the column?



In a column, right-click and "Pick from drop down list" may help/- Hide quoted text -

- Show quoted text -

My first thought, but OP doesn't want to use that feature.


Gord

On Wed, 2 Apr 2008 15:35:02 -0700 (PDT), JP wrote:

What about

ToolsOptionsEdit tab, check "Enable AutoComplete for cell values"

?

HTH,
JP


On Apr 2, 10:46*am, T. Hulot wrote:
This is exactly what I want, but is there a way to make this a default
setting for an entire column? *Or will I have to right-click and
select this for each and every cell in the column?



In a column, right-click and "Pick from drop down list" may help/- Hide quoted text -

- Show quoted text -

OK, what about a dynamic named range in the same column as you are
doing the data entry, then data validation on the entire column? As
soon as you typed something in the column, it would be added to the
dropdown list. At least, that is my theory, completely untested of
course.

--JP

On Apr 2, 8:02*pm, Gord Dibben <gorddibbATshawDOTca wrote:
My first thought, but OP doesn't want to use that feature.

Gord



On Wed, 2 Apr 2008 15:35:02 -0700 (PDT), JP wrote:
What about

ToolsOptionsEdit tab, check "Enable AutoComplete for cell values"

?