Multiple indirect references inside arrayformula

1
votes

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"}))
1
google-sheetsgoogle-sheets-formulaarray-formulas
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.

1 Answers

0
votes

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