1

My problem is: I have a google workbook with different sheets (sheet names are not fixed). I've made some formula to get the list of these names, for example, "sheetAAA" will be written in cell C1, "sheet123" will be written in cell C2 and so on.

My problem now is that I want to aggregate all the data inside all those listed sheets without leaving any blank space between. I've tried using an arrayformula+indirect like arrayformula(indirect c1:c10) but I only get parse errors. It worked with just 1 cell reference though. Do you have any idea to make this work?

=ARRAYFORMULA({indirect("'"&WeekINTERMEDIATE!EE3&"'"&"!A:BA"),indirect("'"&WeekINTERMEDIATE!EE4&"'"&"!A:BA"}))
Improve this question
1
  • Number of colums are variable, some sheets have more some less, and headers are different, so query should not be a possible way...
    – S. R.
    Commented Sep 18, 2019 at 15:56

1 Answer 1

Reset to default
0

try:

=ARRAYFORMULA({
 INDIRECT("'"&WeekINTERMEDIATE!EE3&"'"&"!A:BA");
 INDIRECT("'"&WeekINTERMEDIATE!EE4&"'"&"!A:BA"}))

or:

=ARRAYFORMULA({
 INDIRECT("'"&WeekINTERMEDIATE!EE3&"'"&"!A:BA")&
 INDIRECT("'"&WeekINTERMEDIATE!EE4&"'"&"!A:BA"}))

also note that INDIRECT does not support array ranges

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.