+ Reply to Thread
Results 1 to 7 of 7

How to display data from checkbox list in a summary table

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    Orlando, FL USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to display data from checkbox list in a summary table

    I need to select milestone data (dates, title, status, type) from a table in Excel and display the selected data in a second table. I need to be able to select between 1 and 5 milestones, from a list of from 1 to 11 milestones, and display them in the second table area, used to report the most important ones. I have been using checkboxes and nested if then statements, and pasting into a merged cell, to try and display them. However, there are multiple issues with this approach... if the milestone is a one-liner, the next milestone will not line up with it's other data... if it's a two-line data, that works, because the other data is two-lined. On the final table, where the up-to-5 selected milestones are displayed, the table borders are currently just a group of lines laying over the merged left, center and right data sections. This does not work well, and it would be better if there were distinct cells that the data were in, with cell borders applied.
    I am using the following formula in the sheet:

    =IF(H5,K5&CHAR(10),"")&IF(H6,K6&CHAR(10),"")&IF(H7,K7&CHAR(10),"")&IF(H8,K8&CHAR(10),"")&IF(H9,K9&CHAR(10),"")&IF(H10,K10&CHAR(10),"")&IF(H11,K11&CHAR(10),"")&IF(H12, K12&CHAR(10),"")&IF(H13,K13&CHAR(10),"")&IF(H14,K14&CHAR(10),"")&IF(H15,K15,"")

    A similar formula is in each of the three merged areas, under the titles (1) "Original Date Proposed Date" - 2 lines of data (2) "Description" - 1 or 2 lines of data possible and (3) "Type and Status" - 2 lines of data. See the tables below:

    Formula Sheet.xlsm

    Instead of using the three merged areas. I would like to use individual cells for the data, and put the data in from one of the checked milestones. However, in order to use seperate lines for each selected milestone, the formula for the first destination cell (for Original / Proposed Dates) needs to look at the table with the check boxes, pick the first one that is checked, show the data in the cell, then move the cursor out of that cell, to the next lower cell. Once in the next lower cell, that formula needs to identify that the previous milestone checked box item is already listed, so it knows to move past it, and not repeat it in the current cell. It needs to find the next checkboxed selected milestone, and continue the process until all selected milestones are in the new table. Then it needs to do the same for other two columns of data ("Description" and "Status / Type."

    I am sure there is a way to do this, but I have been beating my head against it for a while now, and I am not figuring it out. Any help is appreciated!!

    Dave
    Last edited by mitchellfamily13; 10-10-2013 at 11:57 AM. Reason: Put in excel sheet

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to display data from checkbox list in a summary table

    I'd recommend using the Cell Link property of the check boxes to build a hidden table and then use RANK()+COUNTIF()-1 to assign ranks based on date and TRUE.

    From there you could autopopulate your final summary with only the lines in that have checked boxes.


    If you are able to desensitize and post an example of your spreadsheet for me to work with, I can show you.

  3. #3
    Registered User
    Join Date
    10-10-2013
    Location
    Orlando, FL USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to display data from checkbox list in a summary table

    Thanks a lot daffodil11! I uploaded the Formula Sheet.xlsm, which is a sanitized version of the two tables I was using. I have uploaded here Formula Sheet 2.xlsm, which is how I would like to handle the data.

    Formula Sheet 2.xlsm

    I am an engineer that uses Excel a lot, but I haven't tried to do anything like this before. I am not sure what you are saying to do, so if you can show me what you mean using the attached file, it would be great.

    Thanks!!
    Dave

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to display data from checkbox list in a summary table

    Sorry for the delay, I had a few reports to run.

    Here's an example of what you could do. The ranking is on the first page for clarity. I'll remake it with the ranking on a separate tab but the result would be the same. I even included the char(10) concatenations.

    FANCY INDEX MATCH.xlsm

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to display data from checkbox list in a summary table

    And here's the final version, all cleaned up.

    FANCY INDEX MATCH - Final.xlsm

    Note: You will need to activate the dates in column B and C to make sure they aren't reading as text. Many of the dates in B definitely were and it created some errors, but once you activate them with F2 and Enter (or double clicking and hitting Enter) they'll register as dates and not just strings. You'll also want to hide (format text as white) the numbers in column H. They are what the INDEX(..MATCH( is keying off of.

    That speadsheet takes me back to the days of working on the HiPath4000, where I got my start in Excel.
    Last edited by daffodil11; 10-10-2013 at 02:11 PM.

  6. #6
    Registered User
    Join Date
    10-10-2013
    Location
    Orlando, FL USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to display data from checkbox list in a summary table

    Thanks so much for your help!! I have been trying to get this for a few days, and it was beating me. Your solution does exactly what I need. If I have any probelms getting it to work in my actual sheet, I come back with a question. Thanks!!

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to display data from checkbox list in a summary table

    Awesome! Glad I was able to help you out.

    If this solves your issue, I highly recommend you click the Thread Options at the top and choose Solved.

+ 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. [SOLVED] Need checkbox to display information from a row of data
    By rach3lmclark in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2013, 02:59 PM
  2. Manipulate and arrange table data into summary list
    By DavidSpackman in forum Excel General
    Replies: 2
    Last Post: 07-28-2010, 12:57 AM
  3. Count in filtered list and display results in table on right of data
    By raydaw in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2009, 05:14 AM
  4. lookup? list data into summary table
    By Joe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-21-2005, 08:30 PM
  5. [SOLVED] script converting list data to summary table
    By matthew kramer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2005, 02:15 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