Summriez all worksheet in summary work sheet

M

miladrezaee

New Member
Joined
May 20, 2018
Messages
7
Hi, Gents
I am not professional in ms excel and So I'm sorry if my question is simple.
I have 4 sheets witch their name is "M1" till "M4", and each worksheet contain a table, which the first column is worker name and others column is work days status
But worker change in these 4 months and so the table is not same!
how can summarise all sheets in the last sheet, in the way all worker, be in that.

the excel file is attached

book.xlxs

Link-2
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes

Book1
BCDE
6NameAvailableAbsentVacation
7Jone30
8Jack2253
9Bob17211
10David2019
M1



Book1
BCDE
6NameAvailableAbsentVacation
7Jone1020
8Jack2622
9Bob2028
10Samir15114
M2



Book1
BCDE
6NameAvailableAbsentVacation
7Jone18210
8Jack2226
9Bob1818
10Samir15213
11Anderson19110
M3



Book1
BCDE
6NameAvailableAbsentVacation
7Jack11217
8Bob2415
9Samir282
10Anderson10515
11David2091
M4



Book1
BCDE
6NameAvailableAbsentVacation
7Jone58230
8Jack811128
9Bob79632
10Samir58527
11Anderson29625
12David401010
Summary


In C7 of Summary enter, copy across, and down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B7:B30"),$B7,INDIRECT("'"&SheetList&"'!"&CELL("address",C$7:C$30))))

where SheetList is the name of a range which houses the relevant sheet names, that is, M1, M2, M3, and M4.
 
Upvote 0
Aladin Akyurek said:
BCDE
NameAvailableAbsentVacation
Jone
Jack
Bob
David

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]

</tbody>
M1​



BCDE
NameAvailableAbsentVacation
Jone
Jack
Bob
Samir

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]26[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]

</tbody>
M2​



BCDE
NameAvailableAbsentVacation
Jone
Jack
Bob
Samir
Anderson

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]

</tbody>
M3​



BCDE
NameAvailableAbsentVacation
Jack
Bob
Samir
Anderson
David

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]

</tbody>
M4​



BCDE
Jack
Samir
David

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #5B9BD5"]Name[/TD]
[TD="bgcolor: #5B9BD5"]Available[/TD]
[TD="bgcolor: #5B9BD5"]Absent[/TD]
[TD="bgcolor: #5B9BD5"]Vacation[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #DDEBF7"]Jone[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]81[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DDEBF7"]Bob[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]32[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]58[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]27[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #DDEBF7"]Anderson[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

</tbody>
Summary​



In C7 of Summary enter, copy across, and down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B7:B30"),$B7,INDIRECT("'"&SheetList&"'!"&CELL("address",C$7:C$30))))

where SheetList is the name of a range which houses the relevant sheet names, that is, M1, M2, M3, and M4.
Click to expand...

It's working for me, Thank you very much
But I just have another question, how to generate te the unique list of the employee the first column of the summary sheet from the first column of all the tables in previous sheets! it can help me very much
 
Upvote 0
miladrezaee said:
It's working for me, Thank you very much
Click to expand...

You are welcome.

But I just have another question, how to generate te the unique list of the employee the first column of the summary sheet from the first column of all the tables in previous sheets! it can help me very much
Click to expand...

Is it admissible for you to invoke a user-defined function (a function written in vba) in order to get that job done?
 
Upvote 0
miladrezaee said:
I am familiar with VBA, but I am not the professional code writer
Click to expand...

We have the following sheets: M1, M2, M3, and M4.

Column B of each sheet contains the data of interest (the names).

1. Install the following user-defined function as a module, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

2. Define M1name in the Name Manager as referring to:

=M1!$B$7:INDEX(M1!$B:$B,MATCH(REPT("z",255),M1!$B:$B))

3. Define M2name in the Name Manager as referring to:

=M2!$B$7:INDEX(M2!$B:$B,MATCH(REPT("z",255),M2!$B:$B))

4. Define M3name in the Name Manager as referring to:

=M3!$B$7:INDEX(M3!$B:$B,MATCH(REPT("z",255),M3!$B:$B))

5. Define M4name in the Name Manager as referring to:

=M4!$B$7:INDEX(M4!$B:$B,MATCH(REPT("z",255),M4!$B:$B))

6. Define Names in the Name Manager as referring to:

=arrayunion(M1name,M2name,M3name,M4name)

7. Define Ivec in the Name Manager as referring to:

=ROW(INDIRECT("1:"&COLUMNS(Names)))

8. In B5 of Summary control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Names=""),MATCH(Names,Names,0)),Ivec),1))

9. In B7 of Summary control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$7:B7)>$B$5,"",INDEX(Names,SMALL(IF(FREQUENCY(IF(1-(Names=""),MATCH(Names,Names,0)),Ivec),Ivec),ROWS($B$7:B7))))

We have now a dynamic unique list of names in column B of Summary as you wanted to have.
 
Upvote 0
Aladin Akyurek said:
We have the following sheets: M1, M2, M3, and M4.

Column B of each sheet contains the data of interest (the names).

1. Install the following user-defined function as a module, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

2. Define M1name in the Name Manager as referring to:

=M1!$B$7:INDEX(M1!$B:$B,MATCH(REPT("z",255),M1!$B:$B))

3. Define M2name in the Name Manager as referring to:

=M2!$B$7:INDEX(M2!$B:$B,MATCH(REPT("z",255),M2!$B:$B))

4. Define M3name in the Name Manager as referring to:

=M3!$B$7:INDEX(M3!$B:$B,MATCH(REPT("z",255),M3!$B:$B))

5. Define M4name in the Name Manager as referring to:

=M4!$B$7:INDEX(M4!$B:$B,MATCH(REPT("z",255),M4!$B:$B))

6. Define Names in the Name Manager as referring to:

=arrayunion(M1name,M2name,M3name,M4name)

7. Define Ivec in the Name Manager as referring to:

=ROW(INDIRECT("1:"&COLUMNS(Names)))

8. In B5 of Summary control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Names=""),MATCH(Names,Names,0)),Ivec),1))

9. In B7 of Summary control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$7:B7)>$B$5,"",INDEX(Names,SMALL(IF(FREQUENCY(IF(1-(Names=""),MATCH(Names,Names,0)),Ivec),Ivec),ROWS($B$7:B7))))

We have now a dynamic unique list of names in column B of Summary as you wanted to have.
Click to expand...

It is Working, Thank you Aladin
But it has a small bug when I add a new name to M1 or M2 ... , and the summary table is not extended automatically to cover new name, it is not a problem
problem is when I copy down the formula, I most do it to entire of the summary table column, not just for new extendet row
 
Upvote 0
miladrezaee said:
It is Working, Thank you Aladin
But it has a small bug when I add a new name to M1 or M2 ... , and the summary table is not extended automatically to cover new name, it is not a problem
problem is when I copy down the formula, I most do it to entire of the summary table column, not just for new extendet row
Click to expand...

Here is the workbook which implements the set up:
https://www.dropbox.com/s/14wx1cl6lov7tkr/miladrezaee%20Summriez%20all%20worksheet%20in%20summary%20work.xlsm?dl=0

Note that the set up creates a unique list of names obtained across 4 sheets. Note also that the dynamic named ranges
like M1name is based on the fact that the names are text.

miladrezaee said:
I forgot to upload the file
File
Click to expand...

If this file involves an issue with the set up of this thread, see the dropbox file I provided. If it involves a different problem,
you should start a new thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Combining two subs in one sheet - VBA
Replies
3
Views
322
Joe4
Joe4
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
C
  • Question Question
VBA Copy Sort
Replies
4
Views
375
Keebler
Keebler
spacecaptainsuperguy
  • Question Question
Advice for linked files on network drive
Replies
0
Views
741
spacecaptainsuperguy
spacecaptainsuperguy
S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
217
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top