Need help with conditional lists in excel

February 23, 2013 3:06 PM

I'm creating a workout training program in excel, and I want to show a range of data based on certain conditions. Using Excel 2010 More after the jump.

This is how far I've gotten so far:

On cell B1 I have a category "Workout Type", and on cell C1 I have a drop down list of different workouts (Full Body, Chest, Back, Triceps, etc.)

On another sheet (sheet2) I have a list of the workout order:
B13 Chest
B14 Back
B15 Chest
~
B21 Legs


Back to Sheet1, I wanted to create a condition, if B3="full body", return the items in sheet2!B13:B21.

What I have ALMOST works. My formula so far is:

=IF(B12="Full Body",(Sheet2!B13:B21),"select workout")

Select workout is a temporary false statement.

Now what happens is, the argument B13:B21 only returns the value of B13 (Chest)

How do I get excel to show the contents of B13:B21?

Thanks much, and I hope that makes sense!

posted by FireStyle to computers & internet (9 answers total)

Use an absolute reference in the conditional part of the formula, with dollar sign, then remove the ":B21" part of the Sheet2 reference, like this:

=IF($B$12="Full Body",(Sheet2!B13),"select workout")

Once you got that, copy the cell and paste that cell unto the 7 cells below it. You will see all references in the formula that aren't marked with dollar sign offset by one cell, which is what you want.
posted by gmarceau at 3:29 PM on February 23

Is the result you're looking for a list, like "Chest, Back, Legs, Chest"?
In which case you would need to give Excel more specific instructions, like
IF(B12="Full Body",(Sheet2!B13&", "&Sheet2!B14&", "&Sheet2!B15),"select workout")
etc.
I'm not 100% sure that this is what you're trying to do, but there's a more "dynamic" way to accomplish this here.
posted by bleep at 3:41 PM on February 23

It sounds like you want the user to be able to select a choice from a drop down menu. For that you use "Data Validation". I think you can make the reference to the validation list conditional on B12, so only appropriate choices come up.

Sorry for not being more specific, I'm not currently at a computer with Excel.
posted by paper chromatographologist at 4:57 AM on February 24

@bleep: adding "&" to the equation returns an error.

@gmarceau: That worked, except when I try to add another workout type, such as "Chest/Back" (on sheet2, c13:c21) the condition returns the value TRUE or FALSE.

The formula looks like this:

=IF($C$7="Full Body",(Exercises!$B$13:$B$21),"")=IF($C$7="Upper Body",(Exercises!$C$13:$C$21),"")

The "" is so a false statement leaves the cell blank. And this was pasted from the actual spread sheet so the cells in the previous demonstration won't line up.
posted by FireStyle at 8:44 AM on February 24

If my formula didn't work it could be the quotes are off somewhere. But like I said I'm not sure what the intended result should be so I don't know if that's the solution you're looking for.
posted by bleep at 10:31 AM on February 24

Do you want the results to be returned in a single cell, or is it OK for them to be separated in different cells? If the latter, you can return the range you're referring to (your cells sheet2!B13:B21) by using an array function: before entering your formula, highlight a collection of cells, for example, the cells C3:C11 if that's where you want the results to end up, then type in your formula the same as before and hit CTRL+SHIFT+ENTER to create it as an array function. Assuming you want to rotate the results so they show up in cells C3:K3 instead, you could change your formula to something like:
=IF(B3="Full Body",transpose(Sheet2!B13:B21),"select workout")

Just make sure to highlight the right cells first and hit CTRL+SHIFT+ENTER.

If you want the results to fit into one cell, I think you might be best served using string concatenation: something like
=IF(B3="Full Body",Sheet2!B13 & "," & Sheet2!B14 & "," & Sheet2!B15 & "," & Sheet2!B16 & "," & Sheet2!B17 & "," & Sheet2!B18 & "," & Sheet2!B19 & "," & Sheet2!B20 & "," & Sheet2!B21,"select workout")

That's clunky, but without writing your own function in VBA it might be the easiest way to do it.
posted by albrecht at 12:37 PM on February 25

By the way, in all instances, I think you'll be best served using a VLOOKUP or HLOOKUP instead of series of nested IF statements to return the right results given the value in a particular cell.
posted by albrecht at 12:41 PM on February 25

thanks for the replies... I'll try these out and let you know how it goes!
posted by FireStyle at 11:59 PM on February 25

@albrect that worked! The formatting was incorrect, thats why the "&" wasn't working. Thanks everyone for your help! I'm better at excel now than I was 72 hours ago. Thanks again!
posted by FireStyle at 1:36 PM on February 28