I'm sorry if the title isn't as clear as I'd like it to be.



I'm hoping the concept of what I want is simple, however, because I don't even know the best terminology to describe what I want, it's sort of difficult to Google a solution.

My boss has created a MONSTER of a tracking document that needs to be filled out weekly. This document is entirely in Word 2010 and it's very difficult to manage because it's 11 pages long. Good search terms help navigate it somewhat. I could probably make bookmarks, but another solution presented itself to me recently.

The idea is to turn the tracking document into an Excel workbook. Each section of the document would have its own sheet. The first sheet, however, would be a document that takes information from all of the other sheets into a ready-to-print , pre-formatted document that looks similar to, or even better than what my boss has created.

The problem is that I would like that "cover" sheet to be dynamic. To be more specific, I help manage Group Homes for individuals with disabilities. Some homes have three individuals, mine has six individuals, and the largest two homes have eight individuals.

What that means for the "cover" sheet is that it has to be able to expand based on what has been inputted on the other sheets, so that it is properly sized for three, four, five, six, and any other number of individuals.

Is this something that's possible?

Please let me know if I'm being unclear? I feel like there MUST be a way to do this in Excel, but I can't seem to find anything on the topic.

I can try to mock up an example if that would be helpful.