Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-apigoogle-sheets-query

Get row from different sheets based on a string

I have multiple sheets, to track the # of raffle tickets for each person.

Main sheet:

|-----------|------------------|
|    Name   |     # of tickets |
|-----------|------------------|
|   Adam    |         5        |
|-----------|------------------|
|   Michael |         2        |
|-----------|------------------|
|   Sara    |         4        |
|-----------|------------------|

Sheet 1:

|-----------|------------------|---------------|
|    Name   |     Activity     | # of tickets  |
|-----------|------------------|---------------|
|   Adam    |  Registration    |        2      |
|-----------|------------------|---------------|
|   Michael |  Registration    |        2      |
|-----------|------------------|---------------|
|   Sara    |  Registration    |        2      |
|-----------|------------------|---------------|

Sheet 2:

|-----------|------------------|---------------|
|    Name   |     Activity     | # of tickets  |
|-----------|------------------|---------------|
|   Adam    |  Registration    |        2      |
|-----------|------------------|---------------|
|   Sara    |  Registration    |        2      |
|-----------|------------------|---------------|
|   Adam    |  Extra ticket    |        1      |
|-----------|------------------|---------------|

Question: How do I make so that Main Sheet is updated automatically based on Sheet 1, Sheet 2... Sheet n? So basically the Main Sheet should grab the row of each Sheet based on the Column "Name" and then fetch the value of # of tickets and sum it in main sheet.

Solution

  • Replied your data like this: Main Sheet and 2 sheets of data:

    enter image description here

    In my MainSheet, the formula've used in B1 is:

    =SUMIF(Sheet1!$A$1:$A$3;A1;Sheet1!$C$1:$C$3)+SUMIF(Sheet2!$A$1:$A$3;A1;Sheet2!$C$1:$C$3)

    You will need a SUMIF for each sheet you want to add.

    Function SUMIF