CODEWITHSUNDEEP
google-sheets
Nniinima
Nniinima

Reputation: 3

Google Sheets: how to refer to a row with specific text

-I'm trying to create a google sheet that tracks progress of players in a team.

-Each season has its own tab.

-A tab that combines data from all sheets is needed.

-The players in the seasonal tabs are going to be in different order each season based on their performance, there are also going to be players coming and going so I can't know which row is going to include which player's data.

-The combined table needs to be able to refer to rows in other tabs where the player name is present, and count up total points for them.

Picture 1 is individual season table. Picture 2 is where I was stuck. Picture 3 is the desired output, which was achieved using the answer provided.

Example of seasonal tab

Example of the combined table

Example of desired output:

Player Season 17 Season 18 Total
Player1 1800 880 1680
Player4 100 100 200
Player12 50 21 71
Player2 33 11 44
Player3 2 5 7
Player33 1 2 3

Upvotes: 0

Views: 63

Answers (1)

Alebat
Alebat

Reputation: 26

You can merge all the data in a new sheet by using the following formula:

={FILTER(Sheet1!$B$3:$P, len(Sheet1!$B$3:$P)),
  FILTER(Sheet2!$B$3:$P, len(Sheet1!$B$3:$P)),
  FILTER(SheetN!$B$3:$P, len(Sheet1!$B$3:$P))}`

You have to repeat the FILTER(...) part for each sheet.

In the new sheet, get the list of unique user names with =UNIQUE($A:$A), where A is the column with the player names. You can also use a list you already have instead.

You can then use =SUMIF($A:$A;P1;$B:$B) to compute the grand total for each player, where A is the column containing all the names, B the column containing all the total scores, and P the column containing the UNIQUE player names.

Alternatively, you can use =VLOOKUP(P1, SheetN!$B$3:$N$100, 13, FALSE) in the new sheet to read the total score of the player with the name in the cell P1.

I think that with more advanced magic you may be able to automatically gather the data from all the sheets, but in this case you have to manually type each heet name.

Upvotes: 1

Related Questions