Best Solution to create a Master Spreadsheet

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

Something like:

let
    FolderName = "C:\temp\source",

    Source = Folder.Files(FolderName),
    #"Filtered XLS" = Table.SelectRows(Source, each Text.Contains([Extension], ".xls")),
        
    GetFileData = (Workbook) =>
        let
            #"Promote Headers" = Table.AddColumn(Excel.Workbook(Workbook), "Worksheet Data", each Table.PromoteHeaders([Data])),
            #"Combine Worksheets" = Table.Combine(#"Promote Headers"[#"Worksheet Data"])
        in
            #"Combine Worksheets",

    #"Get Workbook Data" = Table.AddColumn(#"Filtered XLS", "Workbook Data", each GetFileData([Content])),
    #"Combine Workbooks" = Table.Combine(#"Get Workbook Data"[#"Workbook Data"])
in
    #"Combine Workbooks"

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.

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...

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.

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:

let
    Source = Folder.Files("C:\temp\consultants"), //change to actual filepath
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Name", each Text.BeforeDelimiter(_, " Tracker"), type text}}),
    WorkbookData = (MyWorkbook, ConsultantName) =>
    let
        Source = Excel.Workbook(MyWorkbook, null, true),
        WorksheetData = (MyWorksheet) =>
        let
            Worksheet = MyWorksheet,
            #"Renamed Columns" = Table.RenameColumns(Worksheet,{{"Column1", "Client Name"}, {"Column2", "Renewal Date"}, {"Column11", "Commission - Expected"}, {"Column15", "Commission - Actual"}}),
            #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Client Name", "Renewal Date", "Commission - Expected", "Commission - Actual"}),
            #"Added Client Type" = Table.AddColumn(#"Removed Other Columns", "Client Type", each if [Renewal Date] ="Renewal Date" then Text.BeforeDelimiter([Client Name], " Company") else null),
            #"Filled Down Client Type" = Table.FillDown(#"Added Client Type",{"Client Type"}),
            #"Filtered Rows" = Table.SelectRows(#"Filled Down Client Type", each ([Client Type] = "Comission" or [Client Type] = "Fee") and ([Renewal Date] <> "Renewal Date") and ([Client Name] <> null and [Client Name] <> "")),
            #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Client Name", type text}, {"Client Type", type text}, {"Renewal Date", type date}, {"Commission - Expected", Currency.Type}, {"Commission - Actual", Currency.Type}})
        in
            #"Changed Type",
        #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet") and not Text.Contains([Item], "Sheet") and not Text.Contains([Item], "Summary")),
        #"Extracted Worksheet Data" = Table.AddColumn(#"Filtered Rows", "Worksheet Data", each WorksheetData([Data])),
        #"Combined Worksheet Data" = Table.Combine(#"Extracted Worksheet Data"[Worksheet Data]),
        #"Added Name" = Table.AddColumn(#"Combined Worksheet Data", "Consultant Name", each ConsultantName, type text)
    in
        #"Added Name",
    #"Extracted Workbook Data" = Table.AddColumn(#"Extracted Text Before Delimiter", "Workbook Data", each WorkbookData([Content], [Name])),
    #"Combined Workbook Data" = Table.Combine(#"Extracted Workbook Data"[Workbook Data])
in
    #"Combined Workbook Data"

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.