Forum Discussion
Populate Excel Table column with a column from another Excel Table, generates !VALUE#
Implicit intersection is wider concept and worked in Excel before dynamic arrays and even tables were introduced. The difference, in older Excel implicit intersection always worked in behind. In Excel 365 implicit intersection is off by default and we use "@" to initiate it.
Within table "@" means current row. if you stay in Sheet2 on row # 3, =Table1[@Link] that means to take value in Link column of Table1 which is in row #3 in the sheet, independently in which sheet Table 1 is. Other words, implicit intersection of row 3 and column where Table1[Link] is located.
Back to the tables, you may use formula like
=LET( v, INDEX( Table1[Desc], ROW()-ROW(Table2[#Headers] ) ), IF(v="", "", v) )to duplicate the column from another table. Please note, links won't be populated.
Implicit intersection is wider concept and worked in Excel before dynamic arrays and even tables were introduced. The difference, in older Excel implicit intersection always worked in behind. In Excel 365 implicit intersection is off by default and we use "@" to initiate it.
Within table "@" means current row. if you stay in Sheet2 on row # 3, =Table1[@Link] that means to take value in Link column of Table1 which is in row #3 in the sheet, independently in which sheet Table 1 is. Other words, implicit intersection of row 3 and column where Table1[Link] is located.
Back to the tables, you may use formula like
=LET( v, INDEX( Table1[Desc], ROW()-ROW(Table2[#Headers] ) ), IF(v="", "", v) )to duplicate the column from another table. Please note, links won't be populated.
Thanks SergeiBaklan, it has to be modified a little bit, if Table2 has more rows than Table1, for example:
=LET(x, IFERROR(INDEX(Table1[Link], ROW()-ROW(Table2[#Headers])),""), IF(x="","",x))Now is we expand Table2 to have more rows than Table1 and fill the last row of Table1 it works as expected.
davidleal , great to know finally it works, thank you for the feedback.