2007 Structured Reference auto-complete?

SVanDee

New Member
Joined
Feb 18, 2010
Messages
3
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

Board Regular
Joined
Jan 16, 2010
Messages
87
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

New Member
Joined
Feb 18, 2010
Messages
3
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

Board Regular
Joined
Jan 16, 2010
Messages
87
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

New Member
Joined
Feb 18, 2010
Messages
3
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!
 

Forum statistics

Threads
1,081,418
Messages
5,358,567
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top