Maintain link between two tables using structured names as tables move
When linking the values of two tables of the same dimensions (not that it should matter) using structured references in the form of =Table1, the values are correct as long as the second table containing the formula is aligned horizontally with the source table, rows matching up with rows.
However, as soon as either table is shifted up or down, the references act like absolute, unlinked cell references and one or more of the cells returns a #VALUE! error.
As you can see in the pictures, several methods of referencing do not work:
a regular structured reference, i.e. Table1
locked structured reference, i.e. Table1[:]
locked structured reference to current row, i.e. Table1[@:]
(not pictured) a reference to a named range, i.e. =values2016 (this results in a #NAME? error instead for the misaligned rows)
What does work is a simple relative reference to the cell (i.e. C4 or $C4), which the table automatically fills down via AutoCorrect and increments correctly. It continues to update the references as either table moves around in the sheet.
I first encountered this idiosyncrasy while trying to link tables across workbooks, where the destination was offset by one row which meant my data set was offset and ended with one #VALUE! error at the bottom. But this occurs even within the same worksheet as well.
one of the situation
sheet1 ~ presentation sheet with formula and graphics
sheet2 ~ data sheet (multi table)
table1 in sheet1 is a 1:1 table with one of the tables in sheet2
however, according to the presentation, the position of table1 is not as same as the position of table2.
Kenneth Barber commented
You are right that tables are supposed to get rid of the A1 notation, hence the "current row" ([@[Column]]) syntax. This, combined with the "whole column" syntax, help to achieve another goal of tables: the order of the rows shouldn't matter. This means that you can sort your table, move rows around, etc. and the table still works the same.
The way that you are using the tables, you are forcing both tables to be in the same order as each other. They're not meant for that.
And I still don't understand in what case you would need 2 tables to have a 1:1 relationship with each other, with the corresponding rows being in the same position within their respective tables, and where you can't/shouldn't combine them into a single table.
Excel Help commented
Kenneth, I understand the syntax perfectly. As I wrote in my description and showed in the screenshots, I did use @ in one of my trials and it made no difference (and you can try it yourself, too you know!) Regardless, a whole-column reference like Table[Column], when used in a 1:1 relationship between the tables and with formula autofill turned on, DOES WORK. However, it only works when the A1 notation is the exact same in both worksheets. My post is about this second caveat.
The fact that it works only when the tables are aligned in physical space is the issue. Tables are supposed to be independent of their A1 notation and that is the strength and purpose of structured references. It's the result of an oversight; a bug in the program.
User Voice is for feedback on the software, not for soliciting solutions. I didn't ask for a lengthy workaround via helper columns, I'm trying to make the issue visible to the Excel team with the help of the User Voice community.
Kenneth Barber commented
I think that you are misunderstanding the structured reference syntax.
The Table[Column] syntax is a reference to the entire table column. Really, I would think that if a single cell uses =Table[Column], it shouldn't return anything, since it doesn't make sense for a single cell to return an entire column.
The Table[@Column] syntax is a reference to the cell in Table[Column] that is in the same sheet row as the cell that is using the formula. Usually, you only use [@Column] within the same table as Column.
If you need a column to reference all of the values in a column in another table in the exact same order, why not just combine your tables?
If you cannot combine your tables, here is the solution to your problem. Suppose that you want values from Table1 to be referenced in Table2. In Table2, add a column called "Row". In the Row column, use the formula =ROW()-ROW([Row])+1 so that your rows are numbered 1, 2, 3, etc. In the column that is supposed to reference Table1[Column], use the formula =INDEX(Table1[Column],[@Row]) to return the cell in Table1[Column] at position [@Row].
I found this too, really annoying trying to diagnose why in the world only part of my range was returned and the rest had an error, when structured references are supposed to be the answer to mismatched links. Why in the world, in two separate workbooks, do tables need to be in the same row numbers to talk to each other?