Combine multiple excel workbook with multiple sheets

[Anonymous]

Hi community, need help with the following requirement. 
I have a template for project details which needs to be filled everytime I recieve a new project with project details and save it in a folder(One project details only in a template workbook).  I'm attaching a sample for the template below. 

For the sheet "For Automation" - Project Name, Project Code, Client, Revenue, Start Date and End Date columns are coming from the two different sheets from the same workbook i.e Inputs & Summary. 
For each project only one such template workbook can be created and saved in a specific folder.
Now what I want is to consolidate/automate the sheet "For Automation" into a seperate workbook where I can get all the projects in one single place whenever projects templates are added into the specific folder. 

I was thinking if I could use Power Query but I'm not sure how to execute this since the template has multiple sheets. 

Expected Output: 

  Thanks in advance to the community. Quick responses are highly appreciated.  

[Anonymous · Accepted solution]

Hi everyone. I have managed to solve this by myself. If anyone needs help for similar problem please do reach out to me. 

[email protected]

[MRIZ]

Hi, I need help but excel is little more complex!! Can you plz guide me!! thanks

[KT_Bsmart2gethe]

Hi @Anonymous ,

I assuming you the projects files are stored in various folders.

I have a video cover how to combine multiple files from multiple folder location

Combine File 

Sample file attached with the video:

Sample file 

I hope it helps

Regards

KT

[Anonymous]

Hi @KT_Bsmart2gethe , the project files are stored in a single folder. Also the format for all the project templates will be same. Each such project template workbook will have one project. I want to create one workbook/sheet where all the projects are recorded as I have shown in screenshot in the question above automatically everytime a new project template is filled and saved in the folder. 

If there was only one sheet then I would have used "from folder" option and combine it, but here in the template file I have 3 sheets. Therefore I'm not understanding how to work this out. For each new project file saved, the query should access the sheet "For Automation" each time and get data from that sheet.