+ Reply to Thread
Results 1 to 7 of 7

Checkbox function and list creation

  1. #1
    Registered User
    Join Date
    09-08-2020
    Location
    PL
    MS-Off Ver
    2016
    Posts
    4

    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

  2. #2
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    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.
    Attached Files Attached Files
    Last edited by Squeaky; 09-10-2020 at 10:57 AM.

  3. #3
    Registered User
    Join Date
    09-08-2020
    Location
    PL
    MS-Off Ver
    2016
    Posts
    4

    Re: Checkbox function and list creation

    Quote Originally Posted by Squeaky View Post
    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. #4
    Registered User
    Join Date
    09-08-2020
    Location
    PL
    MS-Off Ver
    2016
    Posts
    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. #5
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    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.
    Did I answer your question?

  6. #6
    Registered User
    Join Date
    09-08-2020
    Location
    PL
    MS-Off Ver
    2016
    Posts
    4

    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. #7
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    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.
    Last edited by Squeaky; 09-10-2020 at 11:07 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. LOOKUP function in creation of filtering list of employees
    By megaiooo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2013, 12:52 AM
  2. Formula Creation: Using List Function + Subtraction Function
    By HelpMe! in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2013, 04:52 PM
  3. [SOLVED] Dynamic list creation
    By celiostat in forum Excel General
    Replies: 7
    Last Post: 06-12-2012, 08:09 PM
  4. Dynamic List Creation
    By toboredtosleep in forum Excel General
    Replies: 3
    Last Post: 01-23-2012, 01:50 PM
  5. WO Creation List
    By tim_chisman in forum Excel General
    Replies: 2
    Last Post: 02-24-2011, 04:31 AM
  6. List creation
    By akil in forum Excel General
    Replies: 0
    Last Post: 08-23-2010, 04:37 PM
  7. List creation
    By oscar_kelley in forum Excel General
    Replies: 0
    Last Post: 02-27-2008, 07:55 PM
  8. [SOLVED] drop down list creation
    By swesa in forum Excel General
    Replies: 1
    Last Post: 04-15-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1