Structured References

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)