# Checkbox function and list creation

1. ## 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.

m

2. ## Re: Checkbox function and list creation

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.

3. ## Re: Checkbox function and list creation

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!

4. ## Re: Checkbox function and list creation

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?

5. ## Re: Checkbox function and list creation

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.

6. ## Re: Checkbox function and list creation

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...

7. ## Re: Checkbox function and list creation

Hi ms,

My last post does describe how that works however I can get confusing with my descriptions. Let's try this to see if it helps. In A6, place another check box. Label it as SHEEP and link the check box to B6.
Highlight cells C5 through F5 then drag down to row 6 to place the formulas there. In Cell C6 change the name in the quotes to "SHEEP". Now, if you check or uncheck "SHEEP" it will appear and disappear off of the list in F.
Now, look at DOG. When you check it, B turns to TRUE, C populates with DOG, Cell D3 populates with a 3, E also populates with a 3, and DOG appears in the list on F.
When you uncheck DOG, B turns to FALSE, C and D go blank, therefore E does not contain the row number for DOG (3) and because there is no row number it will not appear in the list in F.
F works by using the index function. =index(range,row,column) In a 5 x 5 grid, you have 1-5 down, and 1-5 across. You can set the index parameters. I set my index to be column C only, so it is the length of the spreadsheet down, (rows), and 1 across, (columns).
So in the F formula we see: The range is C:C, the row is the number that appears in column E and the column is 1 -always. This formula "uses" the row number that populates in E to determine which item to display.

I have had to edit this a few times for better clarity.

There are currently 1 users browsing this thread. (0 members and 1 guests)