Cascading Dynamic Dropdown Lists

I have a file with 3000 lines and 4 columns:

1) Kingdom
2) Phylum
3) Class
4) Order

As you can imagine, all of the "order" entries are unique, as well as
every line in the file.

How can I create a cascading dropdown in excel that will allow me to
choose my Kingdom, then have the next dropdown automatically
pre-populate with the appropriate phlyum. Then class will prepopulate,
and then obviously order...

I want to make it simple for a person to make selections from the
shortest possible list.

I was going to do this with named lists and the indirect function, but
there would literally be over one hundred named lists. Is there any
other way to do this?

Thanks!

You may want to look at Data|Autofilter. It works like what you describe.

Debra Dalgleish has some notes:
http://contextures.com/xlautofilter01.html

choop wrote:
>
> I have a file with 3000 lines and 4 columns:
>
> 1) Kingdom
> 2) Phylum
> 3) Class
> 4) Order
>
> As you can imagine, all of the "order" entries are unique, as well as
> every line in the file.
>
> How can I create a cascading dropdown in excel that will allow me to
> choose my Kingdom, then have the next dropdown automatically
> pre-populate with the appropriate phlyum. Then class will prepopulate,
> and then obviously order...
>
> I want to make it simple for a person to make selections from the
> shortest possible list.
>
> I was going to do this with named lists and the indirect function, but
> there would literally be over one hundred named lists. Is there any
> other way to do this?
>
> Thanks!

--

Dave Peterson

That should be:
data|Filter|autofilter.

choop wrote:
>
> I have a file with 3000 lines and 4 columns:
>
> 1) Kingdom
> 2) Phylum
> 3) Class
> 4) Order
>
> As you can imagine, all of the "order" entries are unique, as well as
> every line in the file.
>
> How can I create a cascading dropdown in excel that will allow me to
> choose my Kingdom, then have the next dropdown automatically
> pre-populate with the appropriate phlyum. Then class will prepopulate,
> and then obviously order...
>
> I want to make it simple for a person to make selections from the
> shortest possible list.
>
> I was going to do this with named lists and the indirect function, but
> there would literally be over one hundred named lists. Is there any
> other way to do this?
>
> Thanks!

--

Dave Peterson

This will not solve my problem, (my fault for not putting in a good
enough description :-p )

I have 2 Sheets:

Sheet 1: List that I previously described
Sheet 2: A list with a couple of hundred lines with the following
columns:

A) Species (ALREADY POPULATED)
B) Kingdom (NOT POPULATED)
C) Phylum (NOT POPULATED)
D) Class (NOT POPULATED)
E) Order (NOT POPULATED)

For columsn B-E I want to have a drop down in each, that is
predetermined by the column to the left of it. (the column B dropdown
will just have the 5 kindoms)

Hopefully this makes my question a little clearer.

Thanks!


Dave Peterson wrote:
> That should be:
> data|Filter|autofilter.

Debra Dalgleish shows how to use a dependent list with Data|Validation.


choop wrote:
>
> This will not solve my problem, (my fault for not putting in a good
> enough description :-p )
>
> I have 2 Sheets:
>
> Sheet 1: List that I previously described
> Sheet 2: A list with a couple of hundred lines with the following
> columns:
>
> A) Species (ALREADY POPULATED)
> B) Kingdom (NOT POPULATED)
> C) Phylum (NOT POPULATED)
> D) Class (NOT POPULATED)
> E) Order (NOT POPULATED)
>
> For columsn B-E I want to have a drop down in each, that is
> predetermined by the column to the left of it. (the column B dropdown
> will just have the 5 kindoms)
>
> Hopefully this makes my question a little clearer.
>
> Thanks!
>
> Dave Peterson wrote:
> > That should be:
> > data|Filter|autofilter.

--

Dave Peterson