Google Sheets ADDRESS formula for a RANGE

Question

In Google Sheets, I currently have several sheets that represent classes and for example sake, might look like this:

CLASS1:
A1-Tom
A2-Fred
A3-Betty

CLASS2:
A1-Bruce
A2-Jane
A3-Sandy

Then in another sheet, SUMMARY, my goal is to create an overview showing in a "CSV" format by class the names of the students, something like this:

COLUMN_A:
A1: CLASS1
A2: CLASS2

COLUMN_B:
B1: Tom, Fred, Betty
B2: Bruce, Jane, Sandy

I can achieve what I need in column B with static cell references with the following formula:

=TEXTJOIN(", ",TRUE,CLASS1!A1:A3).

However, with that I need to manually change the sheetnames in the formulae (e.g. CLASS1!A1:A3 to CLASS2!A1:A3 and so on), when I'd just like to be able to copy it down, using the values in column A to stand in for the sheetnames.

I'm at the point where I've managed to use the values in column A to return a result based on the location of the first item in the respective sheets as follows:

=TEXTJOIN(", ",TRUE,INDIRECT(ADDRESS(7,1,4,TRUE,$A1)))

However, this obviously only returns the first item in my list. For it to work in the same manner as my static solution above, I need to make it work for a range. And that's where I'm stuck.

Any help would be very much appreciated.

Answer 1

try dragging:

=TEXTJOIN(", ", 1, INDIRECT("CLASS"&ROW(A1)&"!A1:A3"))