Does Excel Support Drop-Down Menus to Select Information?
 

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 Support Drop-Down Menus to Select Information?
 

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

Does Excel Support Drop-Down Menus to Select Information?
 

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

Does Excel Support Drop-Down Menus to Select Information?
 

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/

Does Excel Support Drop-Down Menus to Select Information?
 

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/

Does Excel Support Drop-Down Menus to Select Information?
 

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/

Does Excel Support Drop-Down Menus to Select Information?
 

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 -

Does Excel Support Drop-Down Menus to Select Information?
 

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 -

Does Excel Support Drop-Down Menus to Select Information?
 

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"

?

Does Excel Support Drop-Down Menus to Select Information?
 

Hi JP - problem there is that it won't show you unique entries - it will show
your list as everything you have entered in that column, and it could also
add unwanted entries into the list. That is why the easiest way would be
enter your list of known entries elsewhere.

"JP" wrote:

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"

?

Does Excel Support Drop-Down Menus to Select Information?
 

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.

Does Excel Support Drop-Down Menus to Select Information?
 

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.

Does Excel Support Drop-Down Menus to Select Information?
 

I think what threw me off is the OP said "use a drop-down menu to
select data that's already been entered in a column" so I assumed that
meant the SAME column as where new data is being entered. Hence the
forays into AutoComplete, Extend Formatting, etc. A simple
misunderstanding, which you seem to have solved.

Thx,
JP


On Apr 3, 4:27*am, Rayashe wrote:
Hi JP - problem there is that it won't show you unique entries - it will show
your list as everything you have entered in that column, and it could also
add unwanted entries into the list. That is why the easiest way would be
enter your list of known entries elsewhere.



"JP" wrote:
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"

?- Hide quoted text -

- Show quoted text -

Does Excel Support Drop-Down Menus to Select Information?
 

Sometimes I don't articulate very well what I want from software.
I either don't know the nomenclature well enough, or...whatever.

But I think everyone's on the same wavelength now. The procedure
demonstrated in this thread worked precisely as I hoped.

Jd


I think what threw me off is the OP said "use a drop-down menu to
select data that's already been entered in a column" so I assumed that
meant the SAME column as where new data is being entered. Hence the
forays into AutoComplete, Extend Formatting, etc. A simple
misunderstanding, which you seem to have solved.

Does Excel Support Drop-Down Menus to Select Information?
 

In general it's more of a problem with written word... very easy to
misunderstand because there's no inflection in these messages, and
none of the back and forth in a verbal conversation where these types
of things would be more easily understood.

Thx,
JP


On Apr 3, 7:37*pm, T. Hulot wrote:
Sometimes I don't articulate very well what I want from software.
I either don't know the nomenclature well enough, or...whatever.

But I think everyone's on the same wavelength now. *The procedure
demonstrated in this thread worked precisely as I hoped.

Jd