using dropdown lists to populate range of cells

[tsiguy96]

I am working on making advanced sheets for strength and conditioning programming, and i need a bit of help...the first thing I am trying to do is make a dropdown on the actual sheet and being able to select a series of programs they can train with, and when you click it, it will auto populate an entire range of cells with the programming information. on the sheet i have attached, under the Master tab, near the top left where it says "Bench Press", under it it says "Programs". I want to make that cell a dropdown list where you can select either 531 or 5x5, both of which are entire tables set up and ready on the Source tab, and when you click say 531 the entire 531 table from the source tab populates the accompanying tables, or if you click 5x5 it changes the cells accordingly. is this possible, and can anyone help with this? we are working on becoming excel masters in terms of strength and conditioning, this is just the beginning! thanks

[mahones403]

I'm in the process of attempting this but haven't fully built my spreadsheet so I'm not there yet. Do you know how to make a drop down? I dont have much time to go through your spreadsheet but it would be something like =vlookup(value,table,vlookup(dropdownbox,table,2,false),false)) and then make a list somewhere in your sheet that has the dropdown names the column # next to it that you want(hence the 2 in the second vlookup).

[tsiguy96]

if you look at the template (OL, UB, UP etc) and the exercises, those are both dropdowns dependent on each other. (Aka you pick the template, say UP, then the exercise like bench press is a list of all upper body pressing execises). however, when you are talking about one dropdown box populating an entire range of cells like we are trying to do (in this case C11:R18), i have no idea where to start.

[ChemistB]

I named your tables _531 and _5x5. (just the data, no headers)
I put a drop down in B14 giving either 531 or 5 x 5.
Then in C11
=IF($B$14=531,INDEX(_531,ROW(A1),COLUMN(A1)),IF($B$14="5 x 5",INDEX(_5X5,ROW(A1),COLUMN(A1)),""))
dragged across and down.
Does that work for you?

[tsiguy96]

that kind of worked, almost perfectly actually, just not quite. ill post what i got when i did that..

[tsiguy96]

not sure if this is gonna work how i want it to, as part of what i need it to do AFTER i select the program is take the % that we are working at * the weight just down and to the lift of the name of the exercise.

[ChemistB]

Not sure what you are looking for. Can you post an example showing manually how you want things filled in? What is it that you don't like about the way the INDEX filled in the table? Is it the %0's at the bottom?

[tsiguy96]

Not sure what you are looking for. Can you post an example showing manually how you want things filled in? What is it that you don't like about the way the INDEX filled in the table? Is it the %0's at the bottom?

a big thing on this is presentation, we want the athletes to know we spent a lot of time putting their card together and make it look as good as we can, so if i could make this work perfectly without the 0% things that would be nice. also, if you see under the excel file where the column says WT, that is a calculation of the % (2 boxes to the left) and the weight) (cell A15). i need that to work as well. im ok with excel but this stuff is way beyond where i am right now but i want to learn how to do it!

[ChemistB]

Okay, let's take a few steps backwards. On the Source Sheet, in the 513 and 5x5 tables, should the columns that currently are giving #REF errors be empty? and then when brought over to the Master Sheet, they would be filled with the weights based on the Percentages and the weight in A15? Does GR come into that calculation or is it separate (like repetitions)?

[ChemistB]

Take a look at this one. See if I'm on the right track. I usually don't log on over the weekends.

[tsiguy96]

wow, thats EXACTLY what i wanted! i was just re-doing my formulas because i forgot i didnt add the formula for WT in there on the one i posted here, but its absolutely perfect (only thing we would need changed is the WT column needs to be rounded to the nearest 5 which is easy).

you are really, really good at this stuff.

[tsiguy96]

after messing around with it i got it more to where we would need it, but this is incredible. thanks so much chemistb.