2007 Structured Reference auto-complete?

[SVanDee]

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?

 

[Colton]

In order for this to work, you need to choose a cell within the table you want to create, go to the Insert Tab and choose Table, and then make the appropriate choices.

I think what is happening is that you don't really have a table, just a named range.

I tried it both ways, and it only worked if I inserted a table.

 

[SVanDee]

Thanks for the prompt reply.

I'm pretty sure the cell I'm referencing from the formula is in a Table. I did do the Insert | Table procedure. When I type "=" in the formula bar and then click on a cell in the Table, the TableTools tab appears. When I click it, the Table ribbon appears and displays the name of the table. Doesn't that indicate that the cell is in a table? However, the text Excel puts in the formula bar is just "=B2" rather than a structured reference.

I have an image on the screen that shows the open worksheet with the table, table ribbon and the formula displayed but I can't figure out how to attach it to this message. The Insert Image tool wants a URL. Can I just point it to a JPG image on my PC? I tried to go to the HTMLMaker at http://www.mrexcel.com/forum/showpost.php?p=1982777&postcount=241 but it says I don't have access rights to it.

 

[Colton]

I've never really used the "Use Table Names in Formulas" feature, but after playing around with it for a little while, It seems a little buggy. For Example if I changed the design of the table, or changed the row height, or pressed Esc while entering the formula, it no longer worked and just changed back to regular cell references.

Sorry I couldn't help.

 

[SVanDee]

Thanks for trying. Actually, I'm just relieved that no one's come back and said, "Well, of course it's not working. Everyone knows you need to xxxx" where "xxxx" is some really obvious thing that I missed!