Forum Discussion
Summerized total overview sheet from multible sheets
Hi Niclas,
This can be done with Power Query (Aka Get & Transform Data in Excel 2016).
NOTE: if you don't have Excel 2016, you have to download and install the https://www.microsoft.com/en-us/download/details.aspx?id=39379.
Power Query has a great tool called (Append), with this tool you can create multiple queries and append them together.
Please watch this https://www.youtube.com/watch?v=TTMODKl6GNY&list=PLHrPHBbDHgT0XiXLpBaREfRpRlx8SyGkf&index=1 to learn more about it.
To take advantage of this tool, you have to create a table (Ctrl+T) from each marked area in each worksheet and name it with a good name, then create a query from each table and then load it into the workbook as connection only.
But before load it as connection only, you have to https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/ the areas in the query editor.
After that, you can append the connected queries into a one master table.
The amazing thing is when you update any area in any worksheet and refresh the master table (right click >> refresh), will be updated.
I've done all this for you, please find the attached file.
Regards
Really appreciate your efforts to support.
What I`m trying to achieve is the following:
Imagine the following scenarios:
1. Each week I get one workbook with one worksheet as referred as "supervisor" looking like in the attached file
2. I received around 6 different workbooks from different people
3. I want make all this different files into one big summarized file.
4. The summarized file needs to be exactly the same layout as the different "supervisor" sheets. Because it can be 6 different supervisors doing activities for the different "areas". So the summery must add rows etc. for each area to be able to fit everything in one big summary.
I will read through the links you send.
Hi Niclas,
The worksheets in its existing layout are difficult to summarize or append because they are not in the proper data set and they have some merged cell which increases the difficultness!
In addition, there is some data below each table in each worksheet (Contractor, Name of person...).
What about them?
Also, what about the headers and its info (Created by, Last reviewed...)?
Do you want to unify them in the summary worksheet?
Hi,
Please see first sheet in the updated file.
I need basically just the marked areas from 6 different workbooks summarized into one big sheet.
Same for the bottom parts. only the marked information summarized from 6 different workbooks into one long list in one file.
Is that possible somehow?
Hi Niclas,
This can be done with Power Query (Aka Get & Transform Data in Excel 2016).
NOTE: if you don't have Excel 2016, you have to download and install the https://www.microsoft.com/en-us/download/details.aspx?id=39379.
Power Query has a great tool called (Append), with this tool you can create multiple queries and append them together.
Please watch this https://www.youtube.com/watch?v=TTMODKl6GNY&list=PLHrPHBbDHgT0XiXLpBaREfRpRlx8SyGkf&index=1 to learn more about it.
To take advantage of this tool, you have to create a table (Ctrl+T) from each marked area in each worksheet and name it with a good name, then create a query from each table and then load it into the workbook as connection only.
But before load it as connection only, you have to https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/ the areas in the query editor.
After that, you can append the connected queries into a one master table.
The amazing thing is when you update any area in any worksheet and refresh the master table (right click >> refresh), will be updated.
I've done all this for you, please find the attached file.
Regards
- Hi Haytham,
Thanks a lot for all the support with this. I really appreciate it!
Take care and talk to you again.
Br
Niclas