Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Combine multiple excel workbook with multiple sheets

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.  



Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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]

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      MRIZ
      Helper II
      1 year ago

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

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.