Multiple indirect references inside arrayformula

Question

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"}))

Answer 1

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