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

-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.

CodePudding user response：

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.