0

I have a problem with the following bit of google sheet work. I have a table with the names of various tabs that contain data I want to sum up using an INDIRECT within an ARRAYFORMULA:

|sheet3|more data|other stuff|sheet77|sheet6|

Each sheet contains a couple hundred rows, and I don't know how many sheets I will need to INDIRECT to.

I'm then using the following formula to add up the data from a specific cell within each of the named sheets:

=arrayformula(SUM(INDIRECT("'"&a1:a99&"'!a1")))

However this only gives me data from the first sheet listed in the list ("sheet3" in the example) and none of the others ("more data", "other stuff", etc...). Is there a way to use INDIRECT in this way to point to the cell in all those tabs?

Alice
  • 1

1 Answers1

0

not possible. only like this:

=ARRAYFORMULA(SUM(INDIRECT(A1&"!A1"); INDIRECT(A2&"!A1"); INDIRECT(A3&"!A1"); 
                  INDIRECT(A4&"!A1"); INDIRECT(A5&"!A1"); INDIRECT(A6&"!A1")))

or like this:

=ARRAYFORMULA(SUM(sheet3!A1; 'more data'!A1; 'other stuff'!A1; sheet77!A1; sheet6!A1))
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    well shoot, there goes that idea, on to plan J then. Thank you very much for the quick answer! – Alice May 03 '22 at 08:58
  • @Alice check the 2nd part: https://stackoverflow.com/a/58314247/5632629 - generating formula with formula – player0 May 03 '22 at 09:11
  • @Alice do not feel pressured to mark the answer as solved especially if it helped you. you are not obligated to do so. – player0 May 03 '22 at 10:30