Forum Discussion

Charly727's avatar
Charly727
Copper Contributor
Mar 21, 2020

MOVE DATA FROM DIFFERENT SHEETS AND TO COMPILE IT IN ONLY ONE TABLE

Hello Everybody

I am trying to create an excel file that takes the information of the different sheets (define range) and to put it automatically in a table. 

The question is that new sheets can be created and I would like to integrate the same range of these new sheets in my table 

I Know there are compilation tool in excel but only for known sheets. I don't know how to include the future ones automatically

 

Thank you in advance

Carlos

 

excel

5 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 21, 2020

    Charly727 

    ExcelExcitingis correct with Power Query.

     

    Something to be considered for the workbook is why there is a need to keep producing new sheets.  Power Query can handle it but why not enter the data into 1 sheet then use Excel's analysis tools (Pivot, subtotal, filters, etc.) to display the data you need?  Something to consider.

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Mar 21, 2020

    HI Charly727,

     

    Power Query is the best solution for your answer, You can convert all you sheets into Excel Tables or open power query select the location of your current excel file and then move use the Power Query to combine the information.

     

    This video link might be helpful - https://youtu.be/F5fjv6nXvZo

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer.

    • Charly727's avatar
      Charly727
      Copper Contributor
      Mar 29, 2020

      ExcelExciting 

       

      Hi Faraz, 

      I have followed the explanations in you video and I have one problem with the updating. 

      When I try to update the power query. Old data are maintained in the power query sheet , even if I delete one sheet the power query after updating maintain those data

       

      Can you help please? It is an important project for me and I am confusing how to repair it

      Carlos

      • ExcelExciting's avatar
        ExcelExciting
        MVP
        Mar 29, 2020

        Hi Charly727 

         

        OK, so here is the game.. 

        Basically power query is ETL tool which means it will

        Extract the information from your source file like XLSX,TXT,CSV,PRN etc

        Transformation is the query editor, where you combine, make changes, shape you data

        Load is finally once your are satisfied with your transformation load it to your workbook/date model and do your stuff.

         

        Note : If you make any changes to your source file & hit refresh it will reflect the changes in the query file. example as you said when you deleted the file it deleted the information from the query file as well.

         

        Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

        If you find the above solution resolved your query don't forget mark as Official Answer

    • Charly727's avatar
      Charly727
      Copper Contributor
      Mar 21, 2020

      ExcelExciting 

       

      Thank you very much Faraz! It is incredible. I didn´t know this application exists 

      I am a bit close to my target because every sheet will contain several tables with details about the same project (every project one template sheet) 

      Could we distinguish the different tables in each sheet and to put all together in different tables?

       

      For example (one table with all energy consumption of every project where can calculate the total, in all table the pending actions of all projects with dates, etc...) 

       

      Thank you very much!

       

      carlos

       

Resources