Summriez all worksheet in summary work sheet

[miladrezaee]

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

 

[Aladin Akyurek]

Book1
	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	30
8	Jack	22	5	3
9	Bob	17	2	11
10	David	20	1	9
M1

Book1
	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	10		20
8	Jack	26	2	2
9	Bob	20	2	8
10	Samir	15	1	14
M2

Book1
	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	18	2	10
8	Jack	22	2	6
9	Bob	18	1	8
10	Samir	15	2	13
11	Anderson	19	1	10
M3

Book1
	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jack	11	2	17
8	Bob	24	1	5
9	Samir	28	2
10	Anderson	10	5	15
11	David	20	9	1
M4

Book1
	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	58	2	30
8	Jack	81	11	28
9	Bob	79	6	32
10	Samir	58	5	27
11	Anderson	29	6	25
12	David	40	10	10
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.

 

[miladrezaee]

	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	30
8	Jack	22	5	3
9	Bob	17	2	11
10	David	20	1	9

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M1​

	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	10		20
8	Jack	26	2	2
9	Bob	20	2	8
10	Samir	15	1	14

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M2​

	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	18	2	10
8	Jack	22	2	6
9	Bob	18	1	8
10	Samir	15	2	13
11	Anderson	19	1	10

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M3​

	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jack	11	2	17
8	Bob	24	1	5
9	Samir	28	2
10	Anderson	10	5	15
11	David	20	9	1

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

M4​

	B	C	D	E
6	Name	Available	Absent	Vacation
7	Jone	58	2	30
8	Jack	81	11	28
9	Bob	79	6	32
10	Samir	58	5	27
11	Anderson	29	6	25
12	David	40	10	10

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.

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

 

[Aladin Akyurek]

It's working for me, Thank you very much

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

Is it admissible for you to invoke a user-defined function (a function written in vba) in order to get that job done?

 

[miladrezaee]

You are welcome.



Is it admissible for you to invoke a user-defined function (a function written in vba) in order to get that job done?

I am familiar with VBA, but I am not the professional code writer

 

[Aladin Akyurek]

I am familiar with VBA, but I am not the professional code writer

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.

 

[miladrezaee]

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.

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

 

[miladrezaee]

I forgot to upload the file
File

 

[Aladin Akyurek]

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

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.

I forgot to upload the file
File

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.