SOLVED

Populate Excel Table column with a column from another Excel Table, generates !VALUE#

Iron Contributor

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 both tables start at the same row, selecting a given column: Table[@Link] should not depend on the location of the table, but it seems that it depends, unless I am doing something wrong.

 

Here is the Table 1:

Screenshot 2023-09-14 at 1.13.09 PM.png

 

This is the Table2:

Screenshot 2023-09-14 at 1.14.19 PM.png

 

The formula used in Link column is:

=IF(Table1[@Link]="", "", Table1[@Link])

 The formula used in Desc column is:

=IF(Table1[@Desc]="", "",Table1[@Desc])

If Table1 and Table2 start on the same row, then first row has the expected result, i.e. no !VALUE# is returned. The IF condition is to avoid returning 0 instead of empty string.

 

Attached the sample file, for a better understanding.

 

Additional question: The hyperlink is lost in Table2,  it can be built via HYPERLINK function, but I am wondering if there is an Excel setting that allows to automatically copy the value with the hyperlink all together.

 

Thanks in advance,

 

David

 

5 Replies

@davidleal 

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.

@Sergei Baklan 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

best response confirmed by davidleal (Iron Contributor)
Solution

@davidleal 

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 @Sergei Baklan, 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.

1 best response

Accepted Solutions
best response confirmed by davidleal (Iron Contributor)
Solution

@davidleal 

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.

View solution in original post