Multi-dependent dropdown lists

Occasional Contributor

I am looking for a way to make the options given in a dropdown list dependent on the outcomes/selections made in multiple other dropdown lists.

For example, dropdown lists in D3 (intent) and E3 (reps) determine the dropdown list options in F3.

If D3 selected "MEDIUMHEAVY" and E3 selected "4", F3 should produce a dropdown list that gives the options "79.00%" and "76.50%", respectively. However if D3 again selected "MEDIUMHEAVY", but E3 selected "2" instead , F3 should produce a dropdown list that gives the options of "83.00%" and "81.00%", respectively.  

30 Replies

@itaroled 

 

I'm attaching an example I created for somebody else. Perhaps you can use this to build your own, just by modifying the table to the left on the spreadsheet. You can change the entries there now to whatever combinations you have in mind.....

 

Please come back with questions if you have any.

Thank you very much! This looks like it will solve my problem but I will definitely return here if anything else is needed!

@mathetes Unfortunately I ran into a snag when plugging my data into the given sample. I sent you a message regarding this problem so you could physically see the problem I am having. Thank you!

@itaroled 

Here is also a file with a dropdown in dependency.

Maybe this will help too.

I will give it a shot and let you know @NikolinoDE
thank you!
@NikolinoDE
I used a combination of your attachment and @mathetes attachment to help solve the problem of creating the multidependent dropdowns so thank you both once again! I have one more issue arising however.
I will need this series of dropdowns to occur roughly 15-20 times in a given sheet, and each series will need to be independent of eachother. Is there a way to copy and paste these throughout a sheet without needing to rewrite their filters, validations, and formulas manually?
Thanks!

@itaroled 

 

Is it safe to assume this reply of yours to @NikolinoDE supersedes the private message you sent me? That, in other words, you've answered the question you were asking me?

 

It would be helpful to me (and I suspect to @NikolinoDE as well) if you could attach a copy of your existing solution with an example or two of those additional sets of multi-dependent drop-downs. That way we could work with what you have. You probably will have to do it again as a private message to each of us.

 

In the absence of that, not seeing how you've done it, my gut tells me that you might want to set up a table with the names of the various drop-downs and their relationships, and then use VLOOKUP to retrieve the name or reference to the appropriate list, embedding that in an INDIRECT. BUT I hasten to add, this is just a gut sense of what might work.

 

I'm giving an example of that VLOOKUP nested in an INDIRECT in cell I8 of the attached spreadsheet. This gets results, in this case from one of two tables of discounts. It can easily be extended.This doesn't have the multi-variable drop down, but using this method in conjunction with whatever you've already done may serve to get the result you want.   Oh, this is also important: you need to make use of named ranges. If you're not familiar with named ranges, here's a good reference.

You should both now have my copy in your inbox! Before redoing it with your suggestions here, I wanted to let you see it as it was with the dropdown lists working (on the relative intensity sheet), with the only problem being the ability to duplicate and create multiple dropdown list series. Let me know if you think the same suggestion applies now that you have seen it, thanks!

@itaroled 

I see it. Can you fill in a few blanks for me...I can generally figure out what I'm looking at, and it seems to work just fine. What exactly are you needing to duplicate? This already handles multiple athletes, right?

 

In short, if you can now describe much more fully what the full functionality is that you're looking for.

  • multiple types of exercise
  • multiple athletes?
  • multiple _________?
In terms of working yes I would agree it does, though if I were to copy the dropdowns to athletes 3, 4, 5, ...etc., I would need to create a new series of filtered lists for each as I did to create athlete #2's dropdown lists and slightly adjust the data validation since I need a new reference list. This process would be entirely too time consuming with a team of 40-100+ athletes, and would take time away from the actual programming piece so I am wondering if creating these dropdowns can be streamlined, so each is not manually created, cell by cell, list by list, etc.
I hope in the end to copy this into a programming dashboard and have the ability to apply these formulae for each exercise for every athlete within a team, potentially 100s of these dropdowns would be required hence the need for a streamlined duplication process.
Hope this makes sense!
@mathetes , please see above, unsure if replies give notifications without tagging in a response.

@itaroled 

Yes, just so you do know, replies give notifications to other posters even without tagging, so long as it's still in a chain in which that other person has posted.

 

Anyway, I find I'm not able to "navigate" the infrastructure of your existing sheet. I can't figure out all of the things that are going on, the relationships between one entry and the next. It seemed to me that if I copied the Data Validation references in columns D through G, so that Athlete2 used the same validations as Athlete1, those dropdowns still worked; why, therefore, did you need to create a second list (and on that basis think you need to create one new set of dropdowns for each successive athlete)??  Then, to compound things, it's the formula(s) in and behind column H where a lot of that behind the scenes data manipulation takes place; I was able to follow it all up to that point, but that formula refers to Table1, and from Name Manager I see Table 1 with both a bunch of values in it, but also with a reference to a huge table on the tab "Dropdown Lists"...... 

 

I would like to figure it out, and given enough time I probably could (by reverse engineering it), but if you'd be so kind as to supply an annotated version of the sheet...just to explain its inner workings, that would save a LOT of time.

@itaroled 

 

Another thought regarding how to avoid having to make multiple copies of that cascading set of dropdowns, or at least another question or two:

 

  • How do you use the "assignment" for an athlete?
  • Does the entire list, for all 100 plus,  get published or posted on a bulletin board?
  • How often do you change things for any given athlete?
  • Even if it changes, say, weekly for any given athlete, might it not make sense to just have a dashboard that calculates for one person at a time, with a printed "assignment" for that person?
  • You perhaps could create a table of athletes and their individual Intent, Reps, Percentage....etc., and just use VLOOKUP (or equivalent) to populate the variables that are now down via dropdown.

 

Even if those specific questions are not the relevant ones--I'm just trying to get a picture of how you actually USE the results of the individual calculations, wondering whether coming at it from a different direction, might resolve your concerns. So if there is still another way to conceive of the process, feel free to describe it. It's the kind of back and forth that is much easier in person than via message boards.

Am forwarding an annotated version to you right now let me know if this helps to clarify!

@itaroled 

 

THANK YOU for that annotated chart. For the sake of others who might be interested in chiming in -- or simply in seeing how a complex series of dropdowns can be created -- I'm attaching that annotated version of your sheet here.

 

I would appreciate your verbal description as well of the formula that comes into play in column H of the "relative intensity chart".  the formula in cell H7 reads as follows (with cell references to the prior columns in that row; those vary for row 5, but the formula is otherwise unchanged)


=FILTER(Table1[SETS],(Table1[INTENT]=D7)*(Table1[REPS]=E7)*(Table1[PERCENTAGE]=F7)*(Table1[VOLUME]=G7))

My difficulty with this is that "Table1"--in Name Manager--is filled with a series of values AND contains a link to the columns containing the primary table of your workbook. So it's not clear to me what that formula is actually doing, yet it's also clear that it's core (or so it seems) to what you're trying to achieve.

 

Would it also be possible to scan and share with us a copy of the printed chart you currently use, the one you've trying to "automate" by means of Excel? Or, if it itself is something available on the internet, give us a link.

 

Another few questions, pertinent to the questions I asked a couple messages back....

  • When you start or modify the exercise routine for a given athlete, what are the main inputs you get FROM that athlete?
  • What questions do you ask him or her?
  • How do you determine where to start in your entry under "Intent"?

I ask those, again, just wondering if a different "starting point" would minimize or eliminate the need for replication with slight modification. I'm trying to get a more complete picture/understanding of the human or management process surrounding the use of the chart and hoped for Excel workbook. There's always a "bigger picture" -- just dealing with the spreadsheet itself is liable to getting lost in the trees and not seeing the forest.

@mathetes @itaroled 

Not sure If I followed the entire conversation, but it I believe the attached file (Sheet1) contains a solution that you have been discussing. It requires a mix of data validation list for each individual (percent and volume) and one that can be combined for the relationship between Intent and Reps.

 

At least, that how I understood the issue.

 

In the small table at the top, the light orange cells have drop down lists. The light blue cells filter the correct number of sets based on the previous 4 selections.

@mathetes My understanding of the formula in column H, found on the Relative intensity chart sheet, is that it filters the large table 1 for each variable (intent, reps, percentage, and volume) until the outcome "sets" is the only possible answer. I have attached the chart (you can ignore the colors), but it is basically a combination of two charts: Relative intensity chart and the prilepin chart. The prilepin chart specifically outputs the two variables "volume" and "sets", whereas the relative intensity chart deals with "intent", "reps" and "percentages". This was the only way I could see to get the exact combinations to output, very similar actually, to the formula present in your initial example. 

 

It should be noted before I continue that this speadsheet is more or less a home for these formulae and dropdownlists, rather than an actual example of a programming template, or team dashboard for programming. Both of those vary by coach, organization, team, and/or program, which I think speaks to the need for this to be something easily duplicated and manipulated into their individual templates. For example, one coach may have a completely vertical format for their programs, sticking with columns D-H for 90 rows, while another coach may alternate left/right for their exercise programming using D-H and O-S alternatingly, with 50 rows instead. 

 

My idea is that for a team-based template, a coach would create his program with the intent, reps, and % as permanent factors for that microcycle (day/week of training), with the variables left open being volume choice (Low, Optimal, or High in most cases), and the calculated total sets based on aforementioned factors, for the individualization/personalization aspect of training. Most sport strength coaches tend to execute their programs based on some form of the high-low model combined with their preference other miscellaneous models. The high-low model basically says that game day volume is maximal or "high". To prepare for this volume you must incorporate other "high" days throughout the week/s leading to competition, but obviously someone cannot just operate on "high" volume day in and day out. So there must be an alternation of "medium" days, "low" days, followed by "high" days, generally in that pattern. This changes depending on the frequency of games/week but generally for volume management's sake, we try to limit game day intensities to 3-ish days per week in season if we can help it. Again, all dependent on the demands of sport, practice, and the athlete's training age, etc. Because this varies from starters, to bench players, to the injured, from seniors to freshmen, I would like this to be variable per athlete ideally, so someone accumulating a ton of gametime during a week can take on LOW volume and by association low sets, while someone who hasn't played in weeks or all season can take on HIGH volume and by association high sets, to replicate the high, gamespeed stress levels on their body and CNS, of their starting teammates, so they are less likely to be injured playing at that speed randomly throughout the season. I hope this makes sense... Otherwise some coaches perform readiness questionnaires rather than looking at physical data to determine High, Low, Optimal for volume, but that is generally private sector training like personal trainers which would be less likely to use something like this since they already make everything personalized. My hope is this will bring more individualization to the collegiate and professional strength training world, rather than everyone blindly operating on the same program because they lack a practical means of adjusting volume on the fly on a daily basis. 

 

If any other questions let me know I hope this was helpful! 

This is a great solution! Everything seems to work well and duplication is generally seamless since everything is in a table... however please see my most recent reply to @mathetes where I described how abstract an individual coach's program/template may be. By putting it in the table I am unsure that I would have the ability to adjust and manipulate the dropdowns to any cells of my choosing without it messing up his or her previous format. If there is a way to do this within the format you have presented please let me know! I just could not figure out a way when I was toying with it. Thank you for your help!

@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.