How to create a dynamic range/reference that references other sheets which automatically updates and adds to the range/reference

I am using Google Sheets to track students' lesson attendance.

For one teacher, I have a separate sheet for each student.

I would like to create a total attendance sheet that combines the data of all the other specified sheets.


'Sam Student'!A3:J;'Sally Student'!A3:J;'Sophie Student'!A3:J etc

The thing is, I have tried to set up this total attendance sheet so that it will automatically update the above reference if I add another sheet for another student.

So, if I add the student, Simon Student, the abole reference will automatically become:

'Sam Student'!A3:J;'Sally Student'!A3:J;'Sophie Student'!A3:J;'Simon Student'!A3:J.

The way I have tried to this is by creating a list of all the students in a separate Google Sheets file and pulling this using IMPORTRANGE:

IMPORTRANGE("url","Student database!namescolumn")

Then, I use TEXTJOIN to create the formula:

IMPORTRANGE("url","Student database!namescolumn"))&"'!B3:J"

Which gives me the text string:

'Sam Student'!A3:J;'Sally Student'!A3:J;'Sophie Student'!A3:J;'Simon Student'!A3:J.

And which automatically updates when I add a student name to the student database.

The problem is, I can't use the above formula as a range in, say, a Query function like this:

IMPORTRANGE("url","Student database!namescolumn"))&"'!B3:J"},"Select * where Col1 is not null order by Col1",0)

However, the exact same function works when I type in the range manually:

=QUERY({'Sam Student'!A3:J;'Sally Student'!A3:J;'Sophie Student'!A3:J;'Simon Student'!A3:J},"Select * where Col1 is not null order by Col1",0)

My question is:

How can I create a reference like the above that updates dynamically when I create a new sheet and new student.