Glitch with Structured References

[DixiePiper]

I've searched every way I can think to phrase this question and I cannot come up with a solution. I have a large macro-enabled template that uses a lot of tables for data lookup. I'm used to writing my formulas using the table names and column headers to avoid having to switch back and forth between tabs. For some reason, in this workbook, when I type out the formula, it will pull the table name but it doesn't give the drop down of column headers. I've verified that "use table names in formulas" has been checked. I started to rebuild the workbook but only a handful of tabs in and I was running in to the same issue. I created a simple test workbook and everything works just fine. Any idea what might be going on and how to fix it?

I'm running Excel 365 in a Windows environment.

Thanks in advance for any help.

 

[RoryA]

Once it starts happening, does it affect all tables in the workbook, or only certain ones?

 

[DixiePiper]

Once it starts happening, does it affect all tables in the workbook, or only certain ones?

All of them. For example, I use INDEX + MATCH formulas and I can reference any table in the workbook but it will not give me the column headers. If I click over to the tab with the table, I can select the column and it accepts the structured reference and the formula works just fine. It's just an absolute pain to have to click back and forth between tabs.

 

[RoryA]

Any chance you could put the file (or a replica that shows the problem) somewhere that I can have a look at it? Eg OneDrive or Dropbox, or similar.

 

[DixiePiper]

Any chance you could put the file (or a replica that shows the problem) somewhere that I can have a look at it? Eg OneDrive or Dropbox, or similar

Here's a link to a version of the file: Rate Development SAMPLE.xltm

 

[RoryA]

Ok, I’ll try and have a look tomorrow if nobody else jumps in, as it’s midnight here.

 

[DixiePiper]

Ok, I’ll try and have a look tomorrow if nobody else jumps in, as it’s midnight here.

I think I figured it out. All of a sudden Excel doesn't like table names with dots (e.g. Cost.Center). I just started playing around with it and as soon as I changed the table name, it gave me the column name drop down. Good news: I don't have to rebuild the sheet; Bad news: I have to comb through the entire thing and change all the names, of which there are a lot.

 

[RoryA]

Must be something in a recent update, I'd guess, since you're on 365. It still works in 2016. I wonder if it's to do with the new data types and being able to use a dot to access their elements.

Glad you figured it out though, and thanks for posting the solution - I'm sure others will start running into this, assuming it's not a temporary glitch.

 

[DixiePiper]

Must be something in a recent update, I'd guess, since you're on 365. It still works in 2016. I wonder if it's to do with the new data types and being able to use a dot to access their elements.

Glad you figured it out though, and thanks for posting the solution - I'm sure others will start running into this, assuming it's not a temporary glitch.

I just started fiddling with it while watching Doctor Who reruns, trying to figure out exactly where it went wrong and the minute I used a dot in the table name the "glitch" surfaced. Mind you, it still works with the dot in the table name but you have to actually select the column vs using the autocomplete. But that's a total pain when you're jumping around that many tabs.

Thank you for jumping in to help. I'm always awed by the support I get from this community.