Help with checkboxes and Index

[RdeJonge]

Hello,
I'm a newbie with Excel, and would appreciate some help. I've created an excel spreadsheet for our organization to help with catering.

My first worksheet is the startup and there are a number of Checkbox control objects which the user can choose from options of food. For supper, for example, they can choose between 1-6 choices using checkboxes. The worksheet is quite long because it includes Breakfast, Morning Tea, Lunch, Afternoon Tea, and Supper.

After they've made their choices, I want another spreadsheet to compile just their choices and turn it into a list. So for supper, I would like the spreadsheet to just name the items they've chosen (which ranges between 1 and 6 items). Then we can have a quick glance at it without going through the entire original worksheet.

I have no clue how to do this. My choices (which are all control checkboxes) are in cells C4:C10. They are linked to cells D4:D10. So when ever there is a "TRUE" in cells D4:D10, I want the corresponding item in C4:C10 to be listed vertically. I'm not sure if this is possible because C4:C10 has no text? It only has checkboxes with the corresponding label.

The other problem is that sometimes there will be only 1 TRUE selected, but other times, people choose more than 1 so the resulting list will also be varying lengths, though there is a set maximum.

Would really appreciate any help!

 

[Excellman]

I'm not going to be a lot of help because I'm new too... but I think I can point you in the right direction. If you create a table in columns for each category and then when a checkbox is checked, it then pulls the value from your table and puts it into your 2nd spreadsheet, this may work. For example, you would have a column say... column F with all Breakfast items listed, then column G would have all Morning Tea items listed and column H would have all Lunch items listed and the same for Afternoon Tea, and Supper in columns I and J, respectively. Then you would have a range of info to pull from with a formula something like this: =VLOOKUP(D4,'Sheet1'F2:F27,5,FALSE) ---> this would be for Breakfast then you'd have =VLOOKUP(D5,'Sheet1'G2:G27,5,FALSE) ---> for Morning Tea and so on.

=VLOOKUP(lookup_value, ’sheet_name’!range, col_index_num, range_lookup)

The parameters of the VLOOKUP function are:

• lookup_value – a value that we want to find in another worksheet
• ’sheet_name’!range – a range in another worksheet in which we want to lookup
• col_index_num – a column number in another worksheet from which we would like to pull a value
• range_lookup – default value 0. This means that we want to find an exact match for a lookup value.

You will need to adjust this formula to meet your needs but this could be placed in a cell on sheet2 if you designate sheet1 as where the table is located. So, what's going on here is you are telling Excel to look in a particular column in sheet1 and find the correct food item and then put that food choice in the cell where the formula is on sheet2... say for example, in A2 with formula =VLOOKUP(D4,'Sheet1'F2:F27,5,FALSE) ...maybe scrabbled eggs would go here if they picked it from your checkbox list and it is listed in your table. Then bacon might go in A3 since they picked it and it's in the table as well, and so on. And in say B2 with the formula =VLOOKUP(D5,'Sheet1'G2:G27,5,FALSE) they picked Earl Grey if it is a choice and you have it in your table then that formula would go in B2. Or you could make the list in Sheet2 a long continuous one by having all the food items in one column, one after another. So, you are putting formulas in the cells where you want the food items to show up.... so you could have it like this:
A1 B1 C1
Breakfast Morning Tea Lunch
Scrambled Eggs Earl Grey Ham Sandwich
Bacon Chips
Toast Coke
Oatmeal

Or one long list of all choices...

A1
Scrambled Eggs
Bacon
Toast
Oatmeal
Earl Grey
Ham Sandwich
Chips
Coke

 

[Excellman]

It messed up my tabs above so I made this to show what the output would look like. You would put a formula in each cell on sheet2, like A2 will have a formula and B2 has it's own formula and so on.

A1 - Breakfast	B1 - Morning Tea	C1 - Lunch	D1 - Afternoon Tea	E1 - Supper
Scrambled Eggs	Earl Grey	Ham Sandwich	Green	Steak
Bacon		Chips		Green Beans
Toast		Coke		Baked Potato