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 Power Query add-in.
Power Query has a great tool called (Append), with this tool you can create multiple queries and append them together.
Please watch this video 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 fill down 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 Niclas,
I came back to you again!
With regards to the username who made the last modification, you can depend on the username that is stored in the Excel version of the user.
You can get access to this name through the code as follow:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Range("F4") = Now()
ActiveSheet.Range("F5") = Excel.Application.UserName
End SubThis is the same code as the previous post, but with a new line to retrieve the username.
In this code, I've used the ActiveSheet property to make the code dynamic and apply it only to the active sheet since you have now several sheets in the workbook.
You can use this code to skip the Summerized overview version worksheet from this if you don't want to insert that info to it:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Summary Is ActiveSheet Then
'Do nothing!
Else
ActiveSheet.Range("F4") = Now()
ActiveSheet.Range("F5") = Excel.Application.UserName
End If
End Sub
With regards to how to summarize the worksheets, please tell us how you would like to summarize it?
What data do you want to summarize?
How would you like to have a summary form?
In general, you can use the PivotTable or the Consolidation.
Please visit these links to learn more about that:
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 Power Query add-in.
Power Query has a great tool called (Append), with this tool you can create multiple queries and append them together.
Please watch this video 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 fill down 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
