Structured References

JonBastians 

In this case structured references are not going to give you much.  They have the advantage of allowing you to know the precise extent of your data but, by the time you have combined different sized classes, then the space you need is dictated by the largest group.

The most concise formulation would start with a 3D range with, as you suggest, space for 100 records.  Stacking would give a list of 700 records in which first and last names could be concatenated.  WRAPCOLS would return the 7 columns of 100 records,

= LET(
    stacked,   VSTACK(Table3D),
    firstName, TAKE(stacked,,-1),
    lastName,  TAKE(stacked,, 1),
    fullNames, firstName & " " & lastName,
    WRAPCOLS(fullNames,33)
  )

The alternative, developed in the workbook, would be to use table names one at a time within REDUCE.  HSTACK builds the output array class by class.

=  LET(
     table, REDUCE("", SEQUENCE(1,7),
        LAMBDA(acc,k,
          LET(
            classList, CHOOSE(k, MS_1,MS_2,MS_3,SS_1,SS_2,SS_3,SS_4),
            firstName, TAKE(classList,,-1),
            lastName,  TAKE(classList,, 1),
            fullName,  firstName & " " & lastName,
            HSTACK(acc, fullName)
          )
        )
      ),
    IFNA(DROP(table,,1),"")
  )

Sadly, combining data from an arbitrary number of sheets by formula is not going to be pretty.