Does Excel Support Drop-Down Menus to Select Information?

[T. Hulot]

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

 

[Max]

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

---

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[T. Hulot]

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?

 

[Rayashe]

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 Settings>Allow 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.

 

[Gord Dibben]

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

 

[JP]

What about

Tools>Options>Edit tab, check "Enable AutoComplete for cell values"

?

HTH,
JP

 

[Gord Dibben]

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


Gord

 

[JP]

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

 

[Rayashe]

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.

 

[T. Hulot]

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.

 

[T. Hulot]

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

 

[JP]

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

 

[T. Hulot]

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

 

[JP]

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