Ok, my problem:

I am a manager of several employees. I have created an Excel workbook with numerous sheets that each one of my employees fills out to track a number of different things they are responsible for. Each employee fills out their workbook, and sends it to me. So I have 10 versions of the same workbook, each with different data.

I am trying to create a summary workbook that details all the information in each of the 10 versions of the employee workbook. I would also like to share this "summary workbook" with other managers in my company, all who have a different number of employees under them (some have 5, some 8, etc..).

Now to the problem... I would like the "summary workbook" to have the summary information on the 1st tab, and each employee's data on subsequent tabs, so I don't have to open the individual employee workbooks. I'm not sure how to tackle this. I can make enough sheets for each employee, but if a manager has less employees than the number of sheets, I get broken links. If a manager doesn't save the individual workbooks in the correct directory, I get broken links.

Any help would be appreciated.