SOLVED

Summerized total overview sheet from multible sheets

Brass Contributor

Hi,

If i collect multiple workbooks from a bunch of different people. Containing one sheet as referred "supervisor X" in the attached workbook.

How can I make a final summery of all of the different sheets with the same layout as the referred sheet? 

 

Hope that the information provided is clear enough to understand. 

This is something that I will need to do once every week. Different people will send sheets just like this to me and I have to make a total summery of everything together within one sheet with the same layout as the sheets named as "supervisor".

 

I would also need to collect the name of the user that`s makes the last modifications to the workbook

 

BR

Niclas

7 Replies
Just added an updated file.

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 Sub

This 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:

Consolidate multiple worksheets into one PivotTable

Consolidate data in multiple worksheets

Hello Haytham,
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?

best response confirmed by Niclas Johansson (Brass Contributor)
Solution

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 Haytham,
Thanks a lot for all the support with this. I really appreciate it!
Take care and talk to you again.
Br
Niclas
1 best response

Accepted Solutions
best response confirmed by Niclas Johansson (Brass Contributor)
Solution

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

View solution in original post