Forum Discussion

Dmattes's avatar
Dmattes
Copper Contributor
Jan 20, 2022

Structured References

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...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 21, 2022

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.

JonBastians's avatar
JonBastians
Copper Contributor
Jun 03, 2023
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.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 03, 2023

    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.

    • JonBastians's avatar
      JonBastians
      Copper Contributor
      Jun 04, 2023

      SergeiBaklan 

       

      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!! 

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Jun 04, 2023

        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.

Resources