Checkboxes to automatically populate list below

[Tyrolc]

Hi

I have inserted check boxes to a form im updating. I have linked these checkboxes to a cell. The checkboxes are for a 'table 1' of hazards ( there are 46 of them).

Below table 1 is another table (table 2) that lists the items checked in the table above.

I cant seem to find out how to automatically populate the table below in list form. I can do it for a single checkbox on each line, however there are only 10 rows available as there should be no more than that checked in table 1.

Can someone please let me know how you can automatically fill in these rows?

 

[sijpie]

There could be two ways: one using macros, and the other using formulas.
below is a simple way with formulas.
The image shows the sheet with the checkboxes, the sheet layout shows the formulas used. The checkboxes are linked to the cells in column D. In column E I count the number of Trues up to that point .
In the list in table 2 (Choice) I lookup the order number (starting from 1) in the column E using Match() whcih will give me the first item. I use the number returned by Match() to lookup the corresponding menuitem using the Index() function.

Columns D & E can be hidden

Excel 2010
	A	B	C	D	E
1				columns	to be hidden
2		Menu
3		Starters
4		Salad		TRUE	1
5		Soup			1
6		Main Course			1
7		Chicken		TRUE	2
8		Pork		FALSE	2
9		Beef			2
10		Asparagus		TRUE	3
11		Potatoes		TRUE	4
12		Beetroot			4
13		Desert			4
14		Applepie			4
15		Yoghurt		TRUE	5
16		Fruit			5
17
18
19		Choice:
20	1	Salad
21	2	Chicken
22	3	Asparagus
23	4	Potatoes
24	5	Yoghurt
25	6	-
26	7	-
Sheet4

Cell Formulas
Range	Formula
E4	=COUNTIF(D$3:D4, TRUE)
B20	=IFERROR(INDEX($B$4:$B$16,MATCH(A20,$E$4:$E$16,)),"-")

 

[Tyrolc]

Thank you so much... this was EXACTLY what i needed

 

[sijpie]

What? Even the menu???

 

[jcstauff]

Do you have a recommendation if I need to do something similar to this example, but with about 5,000 lines of information? I need to be able to efficiently scale this function.

 

[sijpie]

How dynamic is your data? If the 5000 lines often change you will need to do something different. If the data is fairly static then the above approach should work. The real effort then is setting it up.
But probably you will have to go to macros to get it done easier and with a more flexible way for output. You can then also use user forms to set up the input side in a more professional way.

 

[jcstauff]

Thanks for the reply! I was able to find a macro code for quickly linking the checkboxes to cells (relatively quickly, it still took a few minutes to run). The problem I'm having now is that this creates a very large file size and is bogging down my spreadsheet. I think I will have to look fro a way to streamline the data/process or look for a different solution to my issue. Thank you!

 

[sijpie]

JC, it will probably be better if you do it through userforms and macros. Any chance of showing how your data is organised? easiest is with some dummy tables. Put borders around the cells and then copy/paste into your reply.