Best Solution to create a Master Spreadsheet

You can use Power Query to combine data from multiple workbooks, really easily.

Something like:

Please Login or Register  to view this content.

You'll probably want to tweak to suit your purposes - feel free to upload a sample workbook, for more specific help.

A shared workbook is not something I'd nomally recommend but if your 'spreadsheets' are single sheet workbooks and each one is only updated by a single individual then having all the sheets in a Shared master workbook might be an option

That master workbook could contain a simple macro to create a single table from all the indvidual sheets' data if necessary.

Olly - I am not sure PowerQuery will work here. The way I read it, the source workbooks will be queried once every hour, and will most likely be open at that time. PowerQuery needs them to be closed, doesn't it?

No, Ali, you can use Power Query to get data from open workbooks.

How come, then, when I try to do so, it tells me I can't because the source workbook is open? What's the trick?

EDIT: Only if it's relevant to this thread, of course!!! I am not hijacking.

Hi Guys!

Thank you for your suggestions, a bit more light on this, I've attached an example of what the Consultants will all fill out themselves with their clients and info. They have different monthly tabs for each consultant.

What would be ideal would be to get the information as regularly as possible onto a master spreadsheet for analysis. I have attached a simple example of what I have drafted thus far for the Master Spreadsheet!

I really appreciate your help guys!

Duncan

Your samples are only partially completed, which doesn't help us definitively understand your requirement.

Can you describe how data maps from each consultant file, into the master file? The column headers don't match.
Where are you sourcing consultant names?
The "Monthly Adjustments" requirement isn't clear at all.

Originally Posted by AliGW

How come, then, when I try to do so, it tells me I can't because the source workbook is open? What's the trick?

EDIT: Only if it's relevant to this thread, of course!!! I am not hijacking.

Might be worth you starting another thread, with your code, Ali, and I'll have a look...

Which source columns contain Expected Commission and Actual Commission?
The source files are split into multiple tables (rows). Which section(s) (row numbers) do you want to include?

If you intend to maintain a map between clients and consultants, how do you intend to manage clients moving between consultants? What happens when Client A moved from Consultant Amanda M to Consultant Anna B? Alternatively, if your filenames are consistent, you could get the Consultant name from the source file name...

You can combine all the consultant data in a folder, using this query:

Combined Consultant Data:

Please Login or Register  to view this content.

Close and load to Data Model, then create a pivot table to display results. Data > Refresh All to update.

See attached workbook for a worked example.

Note, I included your "Comission" typo in the query, for filtering client types. If you're changing this on the source files, be sure to change the filter in the query, too.