Checkbox function and list creation

Dear All,

is there a possibility in MS Excel to create a list with checkboxex and based on that to generate/create a list of those boxes which were checked? So, I would like to achieve the following effect:

1. I check some boxes which are appropriate for me.
2. click on a button
3. I generate a list (could be in a separate sheet) only with those boxes which were checked?

I can copy/past the list which was created to other sheet/document e.g. MS Word.

Would appreciate your help.

m

Originally Posted by Squeaky

Hi ms38,
Here is my solution that does not require a button. In column A, put your check boxes and tie them to a cell. I used the B column to do that.
Column C has a simple true/false equation that will input the name of the item when TRUE.
The D column lists the row number from C when the check box is TRUE. The E column uses the SMALL function to organize the list without spaces. An ARRAY
formula can be used here but they can be slow. The F column will display the name of the item by indexing, using the row number in E. You can make the
list as long as you want and it will still work as long as you drag the formulas in D, E, and F down to as long as your list is. The results in column F
can be placed or mirrored anywhere on any sheet. Hide the cells you do not wish to see.
NOTE: If you add any rows above the first item in the list (CAT in mine) then you will need to adjust the formula in E. For the SMALL function to work properly,
you need a range of values and a number to designate which of the smalls you prefer. = small(range,number). 1 is the smallest, 2 is the second smallest, etc. In my equation I use the
row formula =row()-1 to get the first "small" item. =row() in E2 resolves to "2". I add the -1 to make it resolve to 1. I could have simply typed 1 there,
but then I would have to type 2, 3, 4 etc. in all of the formulas down the column. I prefer to write it this way so the formula can be dragged. I hope I was not confusing here.

Thanks. This is exactly what I wanted to achieve. Many thanks Squeaky!

Squeaky, one more basic question: how did you put in order the column F in your workbook. I mean, how to arrange the formula in a way those checked boxes can be displayed at the top of the sheet, so I could avoid blank cells in between?

Hi ms,
"how did you put in order the column F in your workbook"
That is what the formulas in C, D, and E do to organize F. When you add more items with the check boxes, be sure to drag the formulas in C, D, E, and F down the length of the check boxes
so they will formulate. (In the sheet I sent, the formulas in C end at row 5.) - That is assuming the check boxes are linked to a cell in column B as well.
My basic reasoning is with the formula in C, it will place the item name when B is TRUE, then in C it will place the row number if the box is checked. If not checked it will stay blank.
So the items that are selected will have a unique number next to it in column D and the un-selected items will stay blank. Then, in column E I use the SMALL formula to pick out the smallest
number, then the next smallest number, etc. Since I use the ROW numbers, the numbers that appear in column E are the actual row numbers of the items selected. For instance, the 5 in E,
if you look at the row 5 check box, it is next to Rabbit. Because the numbers in E are the actual rows of the items, I use the index function to "look them up" according to the row number in E.
Did I answer your question?

hi, you partially answered my question. I'll try to be more precise. I'll use an exhample:

When I check box number 1 and box number 4 - in the column F I can see only those two records which are in rows next to each other. In other words the record from the box number 4 goes up and does not stay in the same row. I don't know how to achieve that...