Forum Discussion
Solved
Populate Excel Table column with a column from another Excel Table, generates !VALUE#
I have two tables Table1, Table2, for illustrative purpose of my real scenario, I realized that you can populate the columns from Table2 based on the columns from Table1. It works as expected only if...
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.
That's so called implicit intersection, value is taken from exactly the same row. If you'd like to take the value from same table row counted from table header, that's with INDEX function. But you lost values if sort/filter another table. More reliable any lookup function, but you have to identify your records.
SergeiBaklan thanks for your hint, as per my understanding of Implicit interception operator: @ for Excel tables, the documentation states:
"The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1]"
my understanding is that the same row is relative to the table, i.e. within the table, not related to the position in the sheet. I think the issue is that the at symbol, refers to the current table, not to the referred table, even it doesn't give an error, it doesn't understand it well.
The formula in Table2 for getting the values from Table1:
=IF(Table1[@Link]="", "", Table1[@Link])The at symbol @ is supposed to refer to the values in the current table (Table2), not Table1, but for some reason Excel doesn't complain, changing the columns names from Table2 to avoid having the same names as in Table1, still doesn't get an error, but returns !VALUE# if tables are not at the same row.
My goal is to have simple way to copy one column from one table to another. For some columns I would need to do some transformations, but because Excel Table doesn't accept dynamic array function, it is difficult to achieve it. I can refer to the cell and drag it down, but if the columns in Table1 can increase dynamically which is my case, I would need to expand rows of Table2 enough to cover all the values from Table1.
The other option that I was thinking is to use Power Query, but then you loose the hyperlinks and I don't want to use VBA for adding the hyperlinks, I have seen some workarounds, to add them, but you need the explicit URL in separated place to add them.
Thanks for any advice,
David
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.