Forum Discussion
Multi-dependent dropdown lists
would it not be possible to add another (a third) "dimension" to the orange table, in column H?
No...that would still be in the two-dimensional sheet. A third dimension goes off at right-angles to the two. Mathematicians will sometimes create a third axis, "z" to add to the "x" and "y" axes of a chart, but the charts then take on a three-dimensional (depth) visualization.
A third (in fact even more) is (are) possible in some computer languages (APL comes to mind) but I don't think Excel supports it.
What we could do, if there's a limited number of primary choices, is just add more "orange tables" and label them, for example, A, B, C.... and then the final set of dropdowns would carry names like A12, B23, C45.... but oh, my, that would get difficult to manage. But it could be done.
Also, am working on the formerly requested logic tree now, should be complete by tomorrow morning!
- Actually a visual would be more helpful. A visual "tree" of some kind. And that's just a guess as to what would help with clarity. Although I appreciate your description, I'd almost now say that your words are like leaves on the trees that make it hard to see the forest. I'm probably mixing metaphors there making it difficult to discern the difficulty.
What I was thinking was something like a family tree, but with, in your case, heavier lines where the third generation is hard-wired to the choices of the first two. It is sounding like in the first four steps, the range of choices in each subsequent one is entirely dependent on the combination of the first three; step five then does some kind of "filtering" -- but what does that mean at that point??
If, in fact, all of the first four are hard wired together, might it be possible to create a table of the only possible combinations, and then simply start your whole process with a FILTER function that uses those "only possible combinations" as the criteria? (Or is that what one of the earlier iterations did?)
This really is where, if we were in person, I'd want to be standing with you in front of a white board making all of the steps visible, but somehow stripping away "the leaves"--the actual choices--in favor of just seeing dependencies.
If any of that makes sense. - Also, if this is again too technical I can try to re-simplify and strictly use cell numbers rather than their titles/designated names.
In reference to this "logic tree", the least technical representation I can think of or could generate is likely the large table on "dropdown lists" which has every combination possible using the relative intensity/prilepin chart I attached in my previous post.
Outside of that, I can annotate the needed outcomes with reference to the dropdown lists already established to see if that may help:
1- "INTENT" dropdown list can remain independent, as this will be the first thing a coach would consider when adding an exercise to a program. Choices include MAXIMAL, VERYHEAVY, HEAVY, MEDIUMHEAVY, MEDIUM, MEDIUMLIGHT, LIGHT, and VERYLIGHT.
2- "REPS" dropdown list should be dependent on the chosen intent. I would like to eliminate "impossible" combinations based on the aforementioned chart I attached in the previous post. So this should be unique for each chosen intent, with the combinations only existing where the reps and intent intersect on the chart, and associated set/rep schemes exist. For example on the chart attached formerly, VERY HEAVY and "1 REP" intersect on the chart with associated set/rep schemes being 4x1,7x1,10x1. An example of an impossible combination would be VERY HEAVY and "5 REP" on the chart, because when following the X and Y to their intersection, no set rep scheme is available. Any time the set/rep scheme is not possible for associated combinations, it will be left blank, but if it is possible, it is made clear by the insertion of vertical columns next to the percentages and beneath the Low Optimal High columns.
3- "PERCENTAGE" then is next in the order of variables the coach would consider, so this needs to be dependent on the two former dropdown list choices. If there is a possible combination of the desired INTENT and REP choices, then the PERCENTAGE prescription would be that which is associated with that combination. For visual reference, I will use the original chart that I posted. With the choices being "VERY HEAVY" and "2 REP", the outputted options available in the PERCENTAGE dropdown list should be 92.5% and 90.0%. Since we are only including real and possible combinations of INTENT and REPS, there will not be any thing left blank, only a given selection of percentages. MOST combinations will produce 2 percentages as per the previous example. However, there are some combinations that only allow for 1 percentage option. An example would again be "VERY HEAVY" but with "3 REP" as the desired reps per set. Though 90.0% and 88.0% are both seemingly viable options, only 88.0% is truly an option, because that is where the set/rep scheme columns/bars extend to within the X and Y intersections of INTENT and REPS.
4- "VOLUME" should then be dependent on the previous choices in dropdown lists. LOW, OPTIMAL, HIGH, is generally going to be available for all combinations with exception of "MAXIMAL" (which I will cover all of its exceptions later in this annotation), and for all intents from MEDIUMHEAVY to VERYLIGHT, between the 4 and 5 rep ranges "HIGH" is missing as an option, which you can see in the missing set/rep schemes at those X and Y intersections.
5- "SETS" is then just a filter, that shows where the different selections intersect on the chart, to achieve the desired SET outcome. For example, HEAVY, 4 REPS, 81.0%, and HIGH VOLUME, would produce the 5 SETS.
6- "SET X REP X WEIGHT" is just a sample for the outcome the strength coach may desire to see in a more presentable way, so I concatenated SETS, x, REPS, x, calculation of PERCENTAGE*1RM, lbs, so the coach can see the result of his selections.
6- Exceptions to these combinations largely falls under MAXIMAL as the selected intent, where every combination will only be "HIGH" since it is the highest intent possible. Additionally, only 1 set should be available since if it is "MAXIMAL", only 1 set can/should be able to be done. If more than 1 set can be done with the same weight, it is NOT considered to be MAXIMAL.
7- Last hope for this project is to have the ability to "copy and paste" it throughout the sheet in an abstract way that is without a fixed structure. Though it does not necessarily need to require that function, I am just using this as means of saying the replication process should be fairly simple if possible, since like I said most coaches use excel BUT most coaches are no where near proficient, and generally only understand how to format their sheet, rather than the formulae required for their outcomes. I try to keep everything LOOKING as simple as possible when trying to build things for strength coaches. Most do not care about what is "under the hood", and only concern is that it works for what they want to do, and that it is easy to use!
I hope this helps! mathetes Let me know if this falls in line with the "experimenting" sheet that you recently created and if so we can begin toying with that, and plugging in values from the original chart to get this thing done! Otherwise, maybe (hopefully) something I mentioned in this may have given a clue to what is the bigger picture so we are not getting lost in the trees as you said before.
Thanks again for your help and the help of anyone else who sees this and perhaps can provide a possible solution!
As I said, though, it would be relatively easy to simulate a three-dimensional grid, to have the fourth level of drop-downs addressed by names stored as shown below. The tricky thing is being able to visualize/conceptualize, but IF that's really what's needed: to have a final fourth dropdown list, the content of which is totally dependent on the first three choices, it can be done.
A11 A12 A13 A14 ... (this would be what I've already done)
A21 A22 A23 A24 ..
but then more sets which could be visualized as "behind" that first set
B11 B12 ...
B21 B22 B23...
C11 C12..
..
C34 ....