Pulling data from multiple spreadsheets into a separate spreadsheet

I'm working on a project that involves pulling data from multiple spreadsheets into a separate spreadsheet. I know there are articles available on this type of procedure, but this case is a little different; The data I need from each spreadsheet is not located in the same place in each spreadsheet. I've attached a folder of spreadsheets as an example. There should be 4 test cases and 1 spreadsheet containing my desired outputs.

Notes on the attachment:

1. It is deliberate that the projects are not arranged in the same order on each spreadsheet, and not all projects are included on each spreadsheet.
2. If a project's issue count is not reported on a spreadsheet, I'd like to input 'N/A' as the value on the DesiredOutput spreadsheet.

I'll go ahead and put them in beforehand, so the project and case names will already be populated.

Okay, cool; then what?