Consolidate data from multiple sheets in an overall table on a final sheet

[ayeready]

Hi. Looking for a solution to this in an attempt to consolidate weekly sheets in to a monthly overall value.

I have a spreadsheet with 6 sheets. WK1-5 then a Totals sheet.
Sheets WK1-5 are identical in layout to below.

Book1
	B	C	D	E	F	G	H	I
5	Week1		Possible Sales		Conf Opps		Sales
6	Staff	Sale Number	Grey	Black	Grey	Black	Grey	Black
7	Megan	1587	1	0	1	0	1	0
8	Megan	1588	1	1	1	1	1	1
9	Megan	1599	0	0	1	1	1	1
10	Alison	1601	0	0	1	1	1	1
11	Gail	1605	0	0	1	1	1	1
12	Robbie	1612	0	0	1	1	1	1
13	Megan	1622	1	1	1	1	1	1
14	Sean	1630	1	0	1	0	1	0
WK1

I'm looking to work out how to total each weeks sheet so that there is only one line per staff member and a full total of all 5 weeks figures beside them.

Book1
	B	C	D	E	F	G	H
4	Totals	Possibles		Opportunities		Captures
5	Staff	Grey	Black	Grey	Black	Grey	Black
6	Megan	3	2	4	3	4	3
7	Alison	0	0	1	1	1	1
8	Gail	0	0	1	1	1	1
9	Robbie	0	0	1	1	1	1
10	Sean	1	0	1	0	1	0
11
12
13
14
15
16
Totals

I've attempted a VLOOKUP but can't get it to work and also tried an INDEX/MATCH lookup using arrays to match the staff member to then populate the necessary fields but i think this is a bit above my knowledge levels unfortunately.

Anyone have any ideas?

 

[sandy666]

maybe with Power Query

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8		Staff	P-Grey	P-Black	O-Grey	O-Black	S-Grey	S-Black
Week1		Possible Sales		Conf Opps		Sales			Megan	3	2	4	3	4	3
Staff	Sale Number	Grey	Black	Grey	Black	Grey	Black		Alison	0	0	1	1	1	1
Megan	1587	1	0	1	0	1	0		Gail	0	0	1	1	1	1
Megan	1588	1	1	1	1	1	1		Robbie	0	0	1	1	1	1
Megan	1599	0	0	1	1	1	1		Sean	1	0	1	0	1	0
Alison	1601	0	0	1	1	1	1
Gail	1605	0	0	1	1	1	1
Robbie	1612	0	0	1	1	1	1
Megan	1622	1	1	1	1	1	1
Sean	1630	1	0	1	0	1	0

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Rename = Table.RenameColumns(Table.PromoteHeaders(Table.PromoteHeaders(Source, [PromoteAllScalars=true]), [PromoteAllScalars=true]),{{"Black_2", "Black_1"}, {"Grey_3", "Grey_2"}, {"Black_4", "Black_2"}}),
    Group = Table.Group(Rename, {"Staff"}, {{"P-Grey", each List.Sum([Grey]), type number}, {"P-Black", each List.Sum([Black]), type number}, {"O-Grey", each List.Sum([Grey_1]), type number}, {"O-Black", each List.Sum([Black_1]), type number}, {"S-Grey", each List.Sum([Grey_2]), type number}, {"S-Black", each List.Sum([Black_2]), type number}})
in
    Group

 

[ayeready]

Thanks Sandy.

Forgive me if i've picked you up wrong but would this not only total 1 sheet at a time rather than an overall total for all sheets?

 

[sandy666]

if you post whole example (share excel file) I'll try for all sheets

 

[sandy666]

here is an example for 5 weeks from normalised tables
example

 

[ayeready]

Thanks Sandy.

I'll look at this at some point today but looks as though it works.

 

[sandy666]

You are welcome

Have a nice day

 

[ayeready]

Hi Sandy

Managed to get the PowerQuery to work but was wondering if the totals table could be a live link to the other sheets? i.e. when lines are added/removed/amended this is automatically reflected in the totals sheet.

 

[sandy666]

right click on result table and select Refresh or use Alt+Ctrl+F5

edit:
another way is define Refresh time in connections, from 1 to 999 minutes

 

[ayeready]

Sorry Sandy. Think my knowledge of PoweyQwery is letting me down.

I created the table by consolidating the 5 weeks in to a table. I'm assuming thats the wrong way to do it?