Forum Discussion
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, 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.
12 Replies
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.
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.
- 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.
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.
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.
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
Hi Dmattes
it's like SergeiBaklan 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.
