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.

  • www.Contextures.com has a page exclusively for this, with a more robust solution than then other answer here. The page is titled "Create Dependent Drop Down Lists" and can be found here. – guitarthrower Feb 10 '14 at 20:48
  • You are correct in stating that this should be done in a database. The contextures link is great, but as you say has limitations. The main being that Excel is not a Database. I would suggest moving your solution to something more suited towards your requirements. – guitarthrower Feb 11 '14 at 18:31

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.

  • It wasn't quite as straight forward to extend the concept beyond 2 layers as I first thought. I couldn' keep a single table, but instead had to do it in linked pairs: E.g. a 2 column table for Class to Genus, a 2 column table from genus to species, a 2 column table from species to code. This means that most pieces of information needs to be entered twice. E.g. if I grow a new kind of conifer, I have to add a row to the class-genus table, and a row to the genus-species table. Since this is infrequent, I can put up with it, and it expands gracefully. – Sherwood Botsford Feb 13 '14 at 20:43
  • 1
    To clarify... If you make a pivot table for each needed 2-column relationship, over a master (at the deepest level), all you'd have to do is refresh the pivots after adding a single row to your master. – Madball73 Feb 13 '14 at 22:18

This is the way (I hope the translation works because of my regional version):

  1. Validation data
  2. Allowed by list ("Elenco" in italian version)
  3. Origin: here you have to insert your formula: =if(A1="Conifer";B1:B150;if(A1="Leaf";C1:C150;....)) where in B1:B150 you can find type of Conifer, C1:C150 type of Leaf an so onlist by rules

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.