collate results from a different excel file

I don't see why you want this in a separate spreadsheet. In order to be interactive, all the data and supporting sheets need to be there. We could hide the sheets you don't need to see.

This application uses Excel Tales (because they know how big they are, so when you add new data, all you need to do is refresh the pivot table) and pivot tables and overlaying some pivot table results with named dynamic ranges.

I had a question about the YTD. I assumed it is for the group selected where the item is not completed.

If this is what you are looking for and you need an explanation, give me a shout.

OK, I can copy it all to a separate file and hide those sheets you don't need to see. I'll get on it tomorrow.

Originally Posted by dflak

OK, I can copy it all to a separate file and hide those sheets you don't need to see. I'll get on it tomorrow.

Why not create a copy of the Master sheet in the Report workbook using Get & Transform. That would be updated each time you open the Reports workbook. And no need to have the Master file opened.

Originally Posted by Tsjallie

Why not create a copy of the Master sheet in the Report workbook using Get & Transform. That would be updated each time you open the Reports workbook. And no need to have the Master file opened.

Wouldn't this require that the Master Workbook be present? What would happen if the report were mailed off to someone else?

Should you decide to go with the complete copy, here it is.

There is a control panel sheet that calculates what the Report file name should look like. Fill in the name of the report in Cell B3 and the program will produce a report with that name with a date stamp.

Here's the option with the connection to the Master workbook. No vba needed.
Condition is that the Master workbook must be accessible by the user user the Report workbook (read-only access will do).
If you would be mailing the Report then refresh it before mailing it.
Just change the path to the Master workbook (top-left of the Manager sheet) to yr needs.