Cascading Dynamic Dropdown Lists

[choop]

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]

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

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

 

[Dave Peterson]

That should be:
data|Filter|autofilter.

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!

 

[choop]

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

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]

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

 

[dbahooker]

Excel is crap..

Access can do cascading drop downs like this without writing any code;
out of the box.

-Aaron

 

[choop]

My List would require over 500 named ranges to solve this problem.
Not to mention leaving blanks in my drop-downs. Does anyone have any
other creative solutions ?

The reason I am not using access, is that this list is being
distributed to dozens of people to complete. I do not want to have to
teach them all to use access for this one application.


thanks!

 

[Ryan.Chowdhury]

Here's how you do it.

Requirements:

Each higher order list must be sorted so that common items are
together. So:

A B D G
A B D H
A B E I
A C F J

is acceptable, but

A B D G
A B E I
A C F J
A B D H

would not work

Next, name the first column something. In this example, say "Column1"
name the second column "Column2", "Column3" and finally Column 4

Now on sheet two, you will need to inser two columns between each user
input section. So, Column A is pre-filled with species. [insert two
columns], Column D has drop downs with a special validation that I will
shortly define. [insert two columns]. Column G has drop drowns with a
special validation that I will shortly define...etc.

Not lets talk about Column B and Column C

Column B
Assuming you start the species in A1, B1's formula would be:
MATCH(A1,Column1,0). This gives the position of the first entry for
the species in the species list. Let's say this formula evaluated to
10 meaning that the first occurance of the species in A1 was the 10th
position in "Column1".

Column C
create an array formula that counts the number of occurances of the
species in the species list (named range = "Column1")
{=SUM((Column1=$A1)*1) }. Let's say this formula evaluated to 5
meaning that there were 5 occurances of the species identified in cell
A1 in the list "Column1".

Column D
Specificy the validation as follows:
=OFFSET(Column2,B1-1,0,C1,1)
This basically generates a subset of Column2 beginning in the 10th
position and extending down 5 rows.

Repeat with different column and cell addresses for the remaining cells
and validations.

Ryan