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.

JonBastians 

I see, thank you for the sample file. I misunderstood your task initially. Afraid dynamic expansion of the resulting table based on the size of source table doesn't work. At list with formulae only.

Perhaps to work with the range will be easier. As in column I - in I2 is

=IFERROR(  INDEX(tblMS1[Full Name], ROW()-ROW($I$1) ), "" )

and drag it down till empty cells appear, with some gap.

Same you could do with structured table - fill by formulae like

=IFERROR(  INDEX(tblMS1[Full Name], ROW()-ROW(Table13[[#Headers],[Column1]]) ), "" )

and resize table by dragging the dot at bottom right corner of the table.

Automatically that could be done by Power Query. But it's desirable they have the same structure, i.e. columns First and Last and tables to combine have names of the same structure, e.g. starting from tbl.

Power Query script could be

let
    Source = Excel.CurrentWorkbook(),

    selectTables = Table.SelectRows(
        Source,
        each Text.StartsWith([Name], "tbl")),
    getTableNames = Table.TransformColumns(
        selectTables,
        {
            {"Name", each Text.AfterDelimiter(_, "tbl"), type text}
        }),
    trimNames = Table.AddColumn(
        getTableNames,
        "TrimTexts",
        each Table.TransformColumns([Content],
        {
            {"First", Text.Trim},
            {"Last", Text.Trim}
        } )),
    makeFullName = Table.AddColumn(
        trimNames,
        "FullName",
        each Table.AddColumn(
                [TrimTexts],
                "FullName",
                (q) => Text.Combine({  q[First] , q[Last]}, " ") )[FullName]),
    generateTable = Table.FromColumns(
        makeFullName[FullName],
        makeFullName[Name] )
in
    generateTable

and in attached. I tried to simplify it, but in any case such script requires some coding.

What to use - here is short discussion ,all depends on goals.

Where to learn - quite old feature, not sure. Try google for "excel structured references", first I found

Using structured references with Excel tables - Microsoft Support

Structured references in Excel tables (ablebits.com)