I’m trying to use “auto-complete” to learn more about Structured References to Tables in Excel 2007 (Windows XP SP3 if that matters). A couple of tutorial articles on the web appear to say that if I:
- Select a cell. (I’ve tried this with cells that are and are not in aTable.)
- Type “=” in the formula bar
- Click on a cell that is in a table.
Then Excel will add a Structured Reference to the formula so I can see how they work. Instead, it just fills in a standard, “A1” style reference as if the cell I clicked wasn’t in a table at all.
I verified that the “Formula AutoComplete” and “Use table names in formulas” Excel Options are selected.
I think the table is properly defined and auto-complete is working. The name of the Table is Table1. If I type “=T“ in the formula bar, “Table1” appears in the auto-complete drop-down list and, if I go further to “=Table1[“ a drop-list of the columns in Table1 appears. Excel just won’t fill in the complete Structured Reference when I click a cell in Table1. Have I misunderstood? Isn’t it supposed to do that? Are there more options somewhere that I need to enable?
The particular reason I started down this path is that I don’t understand what use the [#This Row] Special Item is. It only has meaning if the cell where the formula is being entered is in a row that’s included in the Table (although the cell can inside or to the left or right of the Table) and then plain references to the column seem to do the same thing. That is "= TableName[[#This Row],[ColumnName]]" and just plain “= TableName[ColumnName]" seem to both yield the value of the cell at the intersection of the row the cell is in and the column. So is [#This Row] ever actually necessary or it just used to document references more completely?
- Select a cell. (I’ve tried this with cells that are and are not in aTable.)
- Type “=” in the formula bar
- Click on a cell that is in a table.
Then Excel will add a Structured Reference to the formula so I can see how they work. Instead, it just fills in a standard, “A1” style reference as if the cell I clicked wasn’t in a table at all.
I verified that the “Formula AutoComplete” and “Use table names in formulas” Excel Options are selected.
I think the table is properly defined and auto-complete is working. The name of the Table is Table1. If I type “=T“ in the formula bar, “Table1” appears in the auto-complete drop-down list and, if I go further to “=Table1[“ a drop-list of the columns in Table1 appears. Excel just won’t fill in the complete Structured Reference when I click a cell in Table1. Have I misunderstood? Isn’t it supposed to do that? Are there more options somewhere that I need to enable?
The particular reason I started down this path is that I don’t understand what use the [#This Row] Special Item is. It only has meaning if the cell where the formula is being entered is in a row that’s included in the Table (although the cell can inside or to the left or right of the Table) and then plain references to the column seem to do the same thing. That is "= TableName[[#This Row],[ColumnName]]" and just plain “= TableName[ColumnName]" seem to both yield the value of the cell at the intersection of the row the cell is in and the column. So is [#This Row] ever actually necessary or it just used to document references more completely?