Structured References

[Dmattes]

I have a table that is referencing tables from multiple other sheets.  The first formula picks up the structured reference, but each row below that when I try to reference the table in the next sheet, it loses the structured reference and give me the cell reference instead.  I have tried to hard code the formula based on the row above and I get a VALUE error.

[#2]

Hi @Dmattes 

so if I understand correctly, you try to reference a formatted table from within another formatted table, right?

In my opinion it's very unlikely that this would work only in very rare occasions, e.g. if you refer to whole columns, but not just to single cells in the other table.

Maybe you can provide a sample file here or at least some screenshots of the several tables.

[#3]

@Dmattes 

Structured reference assumes you operate with value in current row ("@"), even if you reference from another table in another sheet, or with entire [column].  If value is in current row (e.g. row 3 in sheet 2 in  Table2  reference on row 3 in Sheet 1 from Table 1) it works, otherwise you shall use just cell reference.

But that's not likely scenario. To pickup values in one table from another better to use any lookup function, or at least us use relative reference on position within entire column, like

=INDEX(Table1[columnA], ROW()-ROW(Table2[[#Headers],[columnB]]) )

if to duplicate the values.

[#4]

@Dmattes 

Tables (otherwise known as list object) do not provide any form of structured reference notation for other records in the list.  That is because a list is inherently an unordered collection of records that may be sorted at any time.  Concepts like first, last, prior, next are meaningless when a list may be sorted according to various criteria (unlike arrays which are ordered and elements are referenced by index).

Lookups between Tables are best achieved through the use of foreign keys pairing to the primary key of the Table.  To return the record with a primary key that matches 'foreignKey' one would have

= XLOOKUP(foreignKey, Table1[key], Table1)

= XLOOKUP(foreignKey, Table1[key], Table1[data])

The second returns a specific field within the record.

[#5]

The first 2 rows pull the table (MREXP008) reference from the 1st tab in my file.  All others after that pull the cell reference from the other tabs.  Each tab is set up the same.  The next row should be pulling from table MREXP011

[#6]

Hi @Dmattes 

it's like @Sergei Baklan explained above. Structured reference expect a value in the same row as the current row. This might apply to the first to rows in your example, but probably not for the others.

[#7]

Hi Sergei that worked for me! I want to duplicate 1 column from 7 sheets and compile them into an 8th sheet. I can do that using your formula. How to make my output table dynamically resize as needed to accommodate input from the source table? I am total noob at Structured table references. I thought =Table5[@[Full Name]] should work to bring each row of 'Full Name' from 'Table5' into my new sheet. Sadly not Chat GPT no help at all!! Humans still better! I created a table and used =INDEX(Table5[Full Name], ROW()-ROW(Table13[[#Headers],[Column1]]) ) as you suggested and it works. How to resize is all.

[#8]

@JonBastians 

If that's something like this

i.e. you need to combine few tables (right ones) into one (first), and all tables have the column with unique value among all tables (Key), and you are Excel 365, that could be

=LET(
    keys, VSTACK(
        Table1[Key],
        Table2[Key],
        Table3[Key]
    ),
    words, VSTACK(
        Table1[Word],
        Table2[Word],
        Table3[Word]
    ),
    XLOOKUP([@Key], keys, words, "no such")
)

If something different please provide at least screenshots, sample file without sensitive information is even better.

[#9]

@Sergei Baklan 

Thanks so much for your help! I'm very grateful :)

Please check the attached file.

I have already completed the copying process using Traditional formulas. Now I want to see if it's possible using cell references... As i mentioned, I think it is good if the table can resize as needed as then there will be no chance of my output table losing data (when the input exceeds 30 rows). I'm a teacher and I'm trying to set up systems for tracking students for the whole school. The file is not sensitive (has fake names).

I could easily workaround the issue by setting class size of 100 rows... but I'm trying to learn more about table references, which some consider to be the "proper" way to structure references.

 Beyond a solution to the above scenario, I would be interested to know:

• what you think about Structured Table References vs traditional formulas.
• Could you suggest some learning resources for STR. How did you learn?

Thanks again for your help!! 

[#10]

@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)

[#11]

@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.

[#12]

Thankyou @Sergei Baklan 

I'll try these solutions asap. I read the discussion as well.  I have been getting good results using power query so I think I'll look at M language next to enhance my usage of power query. Thanks again :)

[#13]

Thanks to you too @Peter Bartholomew 

I may well try your solution too. Thanks for pointing out that tables = unordered lists. That makes sense. I had never thought of it that way. So much to learn :)