Copying entries from multiple sheets to new sheet, then summing [Solved/Closed]

Report
Posts
1
Registration date
Sunday November 8, 2015
Status
Member
Last seen
November 8, 2015
-
nahidule
Posts
2
Registration date
Sunday November 1, 2015
Status
Member
Last seen
March 11, 2016
-
I run a weekly pub trivia and I use a spreadsheet to keep track of cumulative scores.

There are 6 sheets in the file...
Week 1
Week 2
Week 3
Week 4
Week 5
Cumulative.

Week 1 through Week 5 has a columns...
Team Name
Round 1
Round 2
Round 3
Round 4
Round 5
Round 6
Total

Sheet 'Cumulative' has columns
Team Name
Week 1
Week 2
Week 3
Week 4
Week 5
Total

What I'm trying to figure out is, and what I want to do is...
A). Copy all unique team names from sheets 'Week 1' through 'Week 5' to the 'Team Name' column of the 'Cumulative' sheet.

B). VLOOKUP (or something comparable) the values from the 'Total' columns of Week 1 through Week 5 of the associated Team Name to the 'Cumulative' sheet, for the associated week... as a extra caveat, if a team didn't play on a given week, ideally a zero would be displayed, if it wouldn't do that already.

C). Sum those values in the 'Cumulative' sheet. (I can do this, just trying to be thorough).

Attached are screenshots of what Week 1 and Week 2 might look like, and a manually entered version of what I'm trying to happen for the 'Cumulative' sheet. Any ideas?

Thanks in advance for any input.





2 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
I think you would need to create a macro to create the cummulative sheet or atleast update the sheet

this is how i would have proceeded
1. copy from sheet1 to sheet5, the team name in a temp sheet, in a single column
2. use advance filter to get the unique list
3. copy the unique name to commulative sheet
then you can use vlookup easily
Posts
2
Registration date
Sunday November 1, 2015
Status
Member
Last seen
March 11, 2016

Nice, helpful resource.