0

I wonder if someone can explain me why I get this weird error in Excel when I reference one table column into another table column. Basically I have a sheet with table called tbl_sett_accounts which looks like on the picture below.

enter image description here

In another sheet I want to reference some of the columns from that table. I have a table tbl_summary on that sheet and when I reference the columns like it is shown on the picture I get error #VALUE!.

enter image description here

If I do reference the same columns outside the table like you see I get the results properly.

enter image description here

I wonder where I make mistake. Is this something that I can do in Excel or I'm referencing it wrong.

If I reference the values just by using the classical way of Sheet and column name and number it works fine.

enter image description here

Thank you for your help.

Improve this question

1 Answer 1

Reset to default
2

With tables, SomeTable[column] means the entire column of the table whereas SomeTable[@column] means the value contained in column at the same row as where the formula is typed.

This @ is called the implicit intersection operator and is not specific to tables and columns. The documentation that covers it is here. Quote:

Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background). The logic works as follows:

  • If the value is a single item, then return the item.
  • If the value is a range, then return the value from the cell on the same row or column as the formula.
  • If the value is an array, then pick the top-left value.

The content for tbl_sett_accounts spans from row 55 to row 57.
The formulas you are typing, wherever you are typing them (same sheet of different sheet) need to be on row 55 to 57 too. To avoid that constraint, you need to use INDEX

For instance, if your formulas start on row 5:

=INDEX(tbl_sett_accounts[Account], ROW() - ROW($5:$5) + 1)

Note how the formula is referencing the entire Account column.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.