If Then Thingie - display range of cells

[Shicaca]

This is probably a pretty easy one, but it is racking my brain.

I have a dropdown menu for a cell with 5 possible text values. Based on the value chosen I want to have a range of 4-5 cells in a column appear with different checkmarked options (like if this option is selected, show this group of cells) ya know?

Based on what option is selected, a different column of checkmarked cells should appear.

THEN I want to have the selected checkmarked values represent percentages. (Like if 3 of the 4 options were selected I want another cell to display 75%).

I need some help here. I don't need the whole thing layed out for me or anything, but a general idea of what formulas to use would be SUPER!

[MSP77079]

How did you create the list of "5 possible text values"? Did you use cell validation with a list? Or is this a combo box?

If you used cell validation for the list, did you use a named range or did you use cell addresses?

What I am thinking is this ...
1. from your description, it makes me think you probably used cell validation
2. that is fine, for each of the 5 choices, create another list (of 4 to 5 items)
3. Name each of these lists with a name
4. the 4 to 5 items to be checked, then, would appear using some INDEX + MATCH functions.
5. if the 5 choices do not make good names, then you might also want another list next to the 5 choices list of how each choice maps to a Name that you gave a list of items.

Am I making any sense?

[Shicaca]

Thanks that helped alot! I'm just not very familiar with lists. Do I want to create them as lists with validation... or just type them out and create a name for them?

I'm not familiar with index/match functions either... yeah I know I am a newbie, any clarification on how to set this up would be greatly appreciated!


Thanks!

[MSP77079]

Any chance you could upload the sheet you are working with? I might be easier to demonstrate than to explain.

If not ... I'll build an example and upload it. Your choice.

[Shicaca]

Ok, based on what attachment is selected I want one of the lists on the bottom to appear in the "attachments" spot. I am using OpenOffice, so let me know if things don't work.

Thanks for all your help!

[MSP77079]

OK. Here is my example. Your appears more practical. I'll do the same with yours and send it back. Meanwhile, you can see the explanations in mine.

[MSP77079]

Here is your file with the formulas required.

[Shicaca]

Thanks for doing that, it's starting to look clearer now.

I'm looking into checkboxes now, and from what I understand the only way to Index checkmarked forms is to create userforms using VBA. I have never done any VBA programming!!

This is turning out to be alot more complicated than I thought, thanks alot for all your continued help!

[MSP77079]

I'm looking into checkboxes now, and from what I understand the only way to Index checkmarked forms is to create userforms using VBA.

Not exactly. You can add check boxes directly to the worksheet. You options (unless you have a Mac; in which case you might have only 1 option):
1. use the Forms toolbar
2. use the Controls Toolbox toolbar

Use View >> Toolbars and pick which toolbar you want to use. There are pluses and minuses. Most people prefer the Control Toolbox toolbar. So, I'll give directions for that one.

Click the first button on the toolbar. This puts the workbook in "design mode". Selected the check box control ... just click the button on the toolbar ... then "draw" the check box where you want it to go. As long as the workbook is in "design mode" you can move it around. You can create several checkboxes; then, select them all (hold down the Ctrl key while you select each one) ... then, use the Drawing toolbar to align them.

The second button on the Controls Toolbox toolbar opens the Properties window, which you use to change the captions (if you want a caption) and adjust the sizes so they all are the same size.

You can set a "Linked" cell for each. This cell will then contain 0 if the item is NOT checked and 1 if it IS checked. Depending on what you want to do with the selections, you might not need an VBA at all. Just use the 0 and 1 in the "linked cells" to let you know what was selected.

Hope this helps.

[Shicaca]

Hey, remember me?

Thanks for the checkbox help. I'm almost done here, just need help with a final touch. I need help on Averages.

I attached my sheet so far. Up top on the right is an average of all the values from the %attached column. I tried a simple =average, but the #div/0's are killing me! The other part is Employee breakdown. I'm not sure if I need a lookup function? But I need an average of each employee's attachments in that column.

I am learning a ton about excel for this project, I just need your help/input on this last thing!

Thanks!