Excel workbook that calculates from external workbooks

I want one Excel sheet in a workbook that calculates values from several (7) other external workbooks. I would prefer that is be done as “formula calculations” but “macro programming” is acceptable as well. I own the work product when completed. This is for calculating the training compliance for volunteers. Each volunteer must take certain training based on their position(s). The position(s) they hold is in 1 workbook, and there is a report for each of the 6 training possible classes. Below are more details: #1. The attached “[login to view URL]” shows a mockup of the resulting report that I am requesting. Below is a description of each column: -ColumnA= Volunteer. Each name is pulled from the “[login to view URL]”workbook. Only adults that have at least 1 position (value in column B-E) are to be shown. These are to be sorted alphabetically. -ColumnB= Category of volunteer (there are 3 categories and this will not change). This is calculated based on the positions they hold as determined in “[login to view URL]” and the table in “[login to view URL]”. -ColumnC-H= Show all of the 6 possible training classes that the 3 categories of volunteers might be required to complete. NOTES: If a volunteer IS required to take a class and has taken it, the date that the class was completed should show up in that cell (these dates are from column E in the 6 external workbooks (Y01, WA01, SFS, S24, S11, S10). If a volunteer IS required to take a class and has NOT taken it, the cell should be RED with no value. If a volunteer is NOT required to complete a specific class, then the cell should be blank. Training Class Y01 in Column C is a special case, and it should show as RED with the date value, EVEN IF it has been completed, BUT the completion date is more than 2 years from today’s date. It should also show up as RED if it has not been completed at all (just like all of the other classes). -The title is static, and the “updated” should be the date that the file is opened. #2. The attached Word Doc “[login to view URL]” shows the Positions and the corresponding training required for that position. The volunteer positions can be grouped into 3 categories (P,S ,C), and each category requires specific training classes. You may (not required) want to put this into Excel (separate workbook, or separate tab in [login to view URL]) for formula reference, but these are static. #3. [login to view URL], generated from another system and will be refreshed. This workbook will always be in the same subdirectory as “317TrainingComplianceReport”. This external workbook is in Excel 97-2003 format and the format of the workbook can be relied on to be consistent. This workbook lists each volunteer, and up to 4 positions that they hold. The volunteer names are NOT sorted alphabetically (but it is close). The order of the positions (columns B-E) may not be consistent, meaning that two volunteers with the same two positions may show those positions in different columns. The columns will always be filled “in order” meaning that column B will be filled first, then column, C, then column D, then column E. The total numbers of volunteers may change, but will never be more than 250. Some volunteers may not hold any positions, and therefore do not require any training. Note that there is always a blank row between volunteer names in this report. #4. The training class status Excel workbooks (Y01, WA01, SFS, S24, S11, S10) are generated from another system and will be refreshed. These workbooks will always be in the same subdirectory as “317TrainingComplianceReport”. These external workbooks are in Excel 97-2003 format and the format of each workbook can be relied on to be consistent. The name of each workbook corresponds to the name of the training class. Note that the row spacing between each volunteer’s names is inconsistent in this report. End.