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.