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

Resources