How do you make a set of cascading dropdown lists in excel?

I'm working on a system to try to take inventory more quickly instead of filling in each row by hand.

The reference data is several hundred rows. The first 3 columns in the reference sheet are Class Genus Species/Cultivar Right now I type in a code, and a bunch of Vlookups fill in the repeating stuff. Lot of codes to remember. Typing on a laptop or touch screen is problematic: On clear days it's hard to read the screen, on cloudy days, your hands are cold and clumsy. On both you are working standing up. At present I use a clipboard and pencil, then transcribe when I get back. The process is error prone.

In the inventory sheet, I want 3 drop down lists, each one populated by the unique items in that list, where the preceeding columns match.

Class, for example, could be any of

Conifer Leaf Tree Food Plant Perennials

Each of these has, say, 150 items. Unwieldy for a drop down list.

The act of choosing conifers, then would populate the the adjacent dropdown list with the unique values of genus. Abies, Larix, Picea, Pinus, Juniperous, Tsuga, Pseudotsuga.

Chosing Picea gives me a list of glauca, meyeri, pungens...

Each dropdown is acting as a filter, limiting the choices of the next dropdown to a reasonable number.

The contextures.com site below is a good clear way to do it, if you have only a small series of dropdown lists, but it would quickly become unmanageable. In that system below, you need to make a sublist for every possible list. This is tedious, and why we invented computers.

So at present, at the top level I have 4 categories. Using his method, this means 5 lists. The conifer's category has 7 genuses. So that makes 7 more lists. The Leaf Tree category has 27 genuses, each with 2-3 species. If I add stuff, I have to add it to the right lists. If you can average 4 members per list, then a 256 item list is 64 4 items lists. Which take 16 4 item lists, which needs 4 four item lists with 1 list on top. And we KNOW it would work out that neatly. I don't want to keep lists by hand.

I would much rather keep the master list just as a simple table.

A           B             C
Conifer     Abies         balsamea
Conifer     Abies         concolor
Conifer     Abies         lasiocarpa var bifolia
Conifer     Abies         veitchii
Conifer     Juniperus     chinensis 'Mint Julep'
Conifer     Juniperus     sabina
Conifer     Juniperus     sabina 'Buffalo'    
Conifer     Larix         laricina
Conifer     Larix         siberica
Conifer     Picea         abies
Conifer     Picea         glauca  
Conifer     Picea         glauca var densitata
Conifer     Picea         marinara
Conifer     Picea         meyeri
Conifer     Picea         omorika
Conifer     Picea         pungens
Conifer     Pinus         aristata
Conifer     Pinus         banksiana
Conifer     Pinus         cembra
Conifer     Pinus         contorta
Conifer     Pinus         monticolo
Conifer     Pinus         mugo
Conifer     Pinus         nigra
Conifer     Pinus         ponderosa var scopulorum
Conifer     Pinus         resinosa
Conifer     Pinus         strobus
Conifer     Pinus         sylvestris
Conifer     Pinus         uncinata
Conifer     Pseudotsuga   mensiesii
Conifer     Thuja         occidentalis
Conifer     Tsuga         mertensiana
Conifer     Tsuga         canadensis

Choosing between 5-10 items is fairly quick. As soon as you have to scroll, your speed goes way down. In this group, the pines have 10 members, two of genuses have only one. While it is not efficient, it does lend it self to reasonable interfaces. (On a larger scale, you'd have to put in some artificial groups in there. E.g. Split the pines into 2 needle pines, and all the rest.

It could be that I using a spreadsheet for what should be done with a database, or some other tool. (Frequently I feel when working with excel that I'm trying to do watch repair with boxing gloves on) However I've run into similar questions to this in enough varied places, that I'm convinced there is a need to do this simply.

Answer

I'm going to assume when you refer to the contextures method, you're talking about the INDIRECT method...

A better method for this sort of complex an multi-tier relationship is the OFFSET/MATCH method, detailed here: http://www.contextures.com/xlDataVal13.html

It only requires list management for each layer.

For layers beyond the second, I simply append the prior layer names together to create a unique path to the lower level.

Note, creating these lists is not as onerous as it seems. It can be automated by making pivot tables over a master list.