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]

honestly I don't know what you did because you didn't say how you did it, what way etc etc.

 

[ayeready]

Issue resolved Sandy. Have created live totals on each weeks sheet using the PowerQuery method and then just a standard multiple sheet lookup to total them.

Thanks for all your help. I normally only use formulas with a (very) little bit of VBA when needed.
Think the PQ stuff may come in handy now that i've figured out how to use it properly.

 

[sandy666]

Mixing PQ and standard formulas aren't a good choice but if it works for you that's ok.

---
each week should be a normalised table
each week should be loaded into PQ
in PQ Append each week table to the one bigger table (Table.Combine)
Group this table by Stuff and for each specified Grey/Black use SUM
that's all

 

[ayeready]

Mixing PQ and standard formulas aren't a good choice but if it works for you that's ok.

---
each week should be a normalised table
each week should be loaded into PQ
in PQ Append each week table to the one bigger table (Table.Combine)
Group this table by Stuff and for each specified Grey/Black use SUM
that's all

Thanks Sandy. I was trying to merge the tables rather than append. Can now link everything.
You're a ??

 

[sandy666]

? I don't understand the icons on the end of your post but assume this is something nice

 

[ayeready]

? I don't understand the icons on the end of your post but assume this is something nice

? ? = Legend

?

 

[sandy666]

silly me, rebus