Glitch with Structured References

DixiePiper

New Member
Joined
Oct 19, 2015
Messages
40
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Once it starts happening, does it affect all tables in the workbook, or only certain ones?
 

DixiePiper

New Member
Joined
Oct 19, 2015
Messages
40
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Ok, I’ll try and have a look tomorrow if nobody else jumps in, as it’s midnight here.
 

DixiePiper

New Member
Joined
Oct 19, 2015
Messages
40

ADVERTISEMENT

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.
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Oct 19, 2015
Messages
40
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,131
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top