Copying entries from multiple sheets to new sheet, then summing

Solved/Closed
polyrhythm Posts 1 Registration date Sunday November 8, 2015 Status Member Last seen November 8, 2015 - Nov 8, 2015 at 10:33 AM
nahidule Posts 2 Registration date Sunday November 1, 2015 Status Member Last seen March 11, 2016 - Nov 9, 2015 at 10:00 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 9, 2015 at 09:46 AM
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
0
nahidule Posts 2 Registration date Sunday November 1, 2015 Status Member Last seen March 11, 2016
Nov 9, 2015 at 10:00 AM
Nice, helpful resource.
0