Forum Discussion
Multi-dependent dropdown lists
itaroled Wow! This thread is quite long with lot's of text and I'm not sure I can read into it. My answer was based on what I thought you needed, being multiple dropdowns on multiple rows, based on a single data set.
You mention that coaches can more or less make up their own programs. Then it will be difficult to capture all if it, with all possible exceptions.
- 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 ....
- I see now! I misunderstood and thought you were using "dimensions" to describe conditions of a given series, as opposed to actual, physical dimensions within the program (2D, 3D, 4D, etc.)
Also, am working on the formerly requested logic tree now, should be complete by tomorrow morning! 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.
- I am just taking a quick glance at the attachment and can respond more fully when I have more time tonight, but I think this has potential to be a great step in the right direction especially if we could introduce more dimensions to the array... that being said, would it not be possible to add another (a third) "dimension" to the orange table, in column H? Or is the limitation existent not there, but rather in the green table with tertiary options? Did not get to sit with this for very long so I apologize if I read over the answer to this question.
I apologize for getting overly complicated and detailed with what goes into it on my end! I have a tendency to get overly technical so thank you guys for calling me on it.
No apology needed. What you're dealing with IS complicated and detailed. And were we face-to-face we'd be able to draw flowcharts etc on a white board, go back and forth with questions and short replies, get clarification before going on.... We could even just sit and watch how you (or coaches involved) go about setting exercise assignments. Who knows, we might even get more buff in the process!
I'm attaching a spreadsheet I just created to play with another approach to a dependent drop down list. This uses two initial entries to create a third reference list that is totally dependent on the first two. I've annotated the spreadsheet with what I hope is a complete enough description.
This uses a two-dimensional matrix to construct those tertiary references. I wish it were possible--maybe it is and I just don't know it--to create a three- or four-dimensional array in Excel. That would enable us to extend the logic basically without limit.
- I apologize for getting overly complicated and detailed with what goes into it on my end! I have a tendency to get overly technical so thank you guys for calling me on it. I will work on a translated version of what will be helpful or usable in terms of this project over the next 12-24 hours! Thanks again.
Well, I can't speak for NikolinoDE or Riny_van_Eekelen BUT I'm feeling quite overwhelmed by the technical aspects of this, to me, utterly unfamiliar realm of human activity. When I use the word "technical" there, I'm most decidedly NOT referring to the Excel or computer challenges involved; rather, I'm reeling from trying to follow your (technical and detailed) description of how these exercise regimens are managed. And it's my own fault, not yours; I asked the questions that got us here...just not realizing how different it is to ask and answer those kinds of "process questions" on an internet forum, rather than in a face-to-face conversation.
I'm wondering if we could come up with a far more mundane analog situation that might represent the multi-dependent dropdown lists and resulting set of figures, but an analog situation that isn't itself highly specific and complicated to the outsider. Or somehow, just remove all of the situational specifics here and say something like
- I need to be able to choose from dropdown list A (consisting of, oh, 8-10 choices)
- Based on choice A, I need to pick from a specific second set of options B (with anywhere from 3 to 10 options, depending on what was chosen in A
- Based on choice B, etc. (for however many iterations is necessary)
- .
- .
- .
- The end result is XXXX (or maybe a set of YYYY components) unique to the prior A-B-C[-D-E-...] combinations.
In other words, a generic description of the logic tree required but without the technical exercise routine specifics. I think that's what I need......
- Right coaches would make up their own programs but most strength coaches in the collegiate realm at least will utilize the chart I attached which is the referenced info in "Table 1", so I am only focused on that for now. It is just making that accessible, and usable for even the least excel-competent coach. Just attempting to speed along the programming process for these coaches, since the time wasted in programming is time that could be better used elsewhere for sure.
