Excel 2007 : Matrixed data gathering with report

[Ecrist]

I am using a spreadsheet to gather data during meetings and I want the report to be created dynamically.

The data gathering is to identify which role(s) performs which task(s) and it is in a matrix format in a worksheet. There will be several (maybe up to 20) of these worksheets in the file to capture different 'modules' of information.

The report is on a separate worksheet within the file and should pull all the information from the other worksheets and create a sortable list. The main sort criteria would be on the role so that I can say "Role 1 performs all of these tasks."

The attached file has three worksheets: an example of a blank data gathering sheet, a completed data gathering sheet, and a report based on the completed data gathering sheet. I am open to modifying the formatting of the sheets to accomplish the goal.

Thanks,
Eric