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
Bookmarks