How can I display data in a listview?

Microsoft Excel: How do you display an auto-expandable array of data that can vary in size from the referenced sheet?

• My goal is to take two sheets of imported raw data and organize all of it based on their date. The one sheet displays a list of dates with data spread across multiple columns, but only one row per date.  The other has multiple rows and columns of data per date(usually between 1-5 rows). On the "dashboard" sheet, I want to display each date in a list form with all of its corresponding data from those two other sheets. My struggle is two fold. 1. How do you pull an array of data into the "dashboard" sheet and 2. under each date, how can you allow the rows to auto-expand in accordance to the imported array from the other sheets. I have tried using INDIRECT, VLOOKUP, and Array formulas, but I am struggling. Here is a depiction of what I described as my goal -

• Answer:

  As usual in Excel, there are a few ways to do this.  Walking you step-by-step and formula-by-formula will take an eternity, so I'm going to assume you're pretty proficient in Excel and can fill in the missing pieces.  Let me know if you have questions. First step is to fill in the blank cells in column A of Raw Data 2 with their actual date values.  You can do this manually, or automatically by selecting blanks only (Select column A -> F5 -> Special -> Blanks) and pasting a formula that references the cell above it. The next step would be to create a "helper sheet" to help you with creating the dashboard.  This would focus on where rows and columns start and finish.  The columns would be:  Date, Number of Rows, Starting Row Date: would be the list of all possible dates. Number of Rows: would be countif([Raw Data 1])+countif([Raw Data 2]) so you know how many lines you have to reserve given the number of dates that appear on the two sheets. Starting Row:  Would just be the sum of all the previously used rows plus whatever title space you have Now that you know the row on which each date starts, you can make a hidden column and use a vlookup() formula to fill in all the dates.  You can make only one date appear by using conditional formatting (if the date isn't the same as the one above it, it shows up). Final step is to actually pull the data.  The first line is easy - if it's the first of a particular date in the column, it automatically pulls from Raw Data 1.  For the second line i'd recommend making a hidden column on the dashboard sheet AND the Raw Data 2 sheet that has a unique ID, e.g., 5/14/2012-1 where "1" indicates that it's the first line item for that particular date.  You can then use this unique ID to pull the line items you need via vlookup(). Hope that helps!