Data and text from multiple sheets into 1 list without blanks!

[daviche]

Hi,
I am fairly new to excel and have taken on a task way over my head. I have 1 workbook with daily worksheets (26 of them). Each worksheet has 8 students listed with drop down lists of time spent with each student. Some days may only have time spent with 1 or 2 of the 8 students.
What I'd like to do is have 8 new worksheets within the same workbook, where each worksheet is devoted to each student, listing just the days that did show time spent with them. I know there has to be a formula to automatically add certain cells to another list once text is added. Can anyone help me with this?
BTW- Child 1's info on Day 1, for example, spans from A9-W13. So I would need to list A9-W13 for each day, excluding days where text wasn't added into cell L9 (other cells automatically have info like name and date).

 

[damianhuerta]

Probably this is not the solution that you have in mind. But, I woud consolidate the data into one worksheet (create a database) and then do a Pivot Table from it. Then you can summarize your data however you want...

 

[daviche]

Sorry for the delayed response. That might be on the right track but I couldn't get it to do what I needed. Any other suggestions? Is there a way to just copy and paste areas (with formulas and formats, margins etc) onto a new worksheet, and have it update whenever the original is updated? That's the minimum of what I need.