Structured references

[knochel]

I have multiple tables in different tabs in my spreadsheets that I reference. Most of the time, when I start writing a formula against a table in a different tab, as I type the table name, a list of possible names shows, and when I type an opening bracket, the list of headers for that table appears. For about 3-months, some of my older tables allow me to see the table name as I type, but the corresponding list of headers does not appear.

 

[Alex Blakenburg]

Is it possible that the problem tables are no longer tables and are just Named Ranges ?
ie if you go to one of the problematic tables and click inside it does the Table Design Tab appear ?

 

[knochel]

Is it possible that the problem tables are no longer tables and are just Named Ranges ?
ie if you go to one of the problematic tables and click inside it does the Table Design Tab appear ?

No, they're definitely tables. I can type the name of the table from a different sheet, and the dropdown list of tables will appear and the table name giving me problems will show. But when I then type in a open bracket to get the headers, I get no dropdown. I can still enter the name of a header (many of my tables have consistent headers) and complete the formula, but not having the dropdown is highly annoying.

 

[Alex Blakenburg]

The only way I have been able to reproduce that behavior is for it to be a Named Range and not a table.
So please confirm your have clicked inside the table and confirmed the table design button shows.
It is still night here so I can't test if you can have Named range the same name as a a table, so maybe check the name mgr for a duplicate.
If still no joy, if you copy the sheet to a new workbook, does the problem carry over to the new book ?
Can you share the workbook ?
Back on line later today.

 

[knochel]

How do I share a file? I'd be happy to email you the file. I don't think a mini-sheet does the job. To confirm, I'm definitely referencing tables, as I can click within one and the Table Design shows up on the menu. I also uploaded an image of my Name Manager; the tables in question are "Ref.XXX" files which reside in the "Reference" tab of a pricing template.

 

[Alex Blakenburg]

Most of the time, when I start writing a formula against a table in a different tab, as I type the table name, a list of possible names shows, and when I type an opening bracket, the list of headers for that table appears. For about 3-months, some of my older tables allow me to see the table name as I type, but the corresponding list of headers does not appear.

It looks like Excel is not happy with a full stop "." in the Table name.
I suspect that the tables it is working on do not have a "." in their name.

Change the name of one of them removing, the full stop and see if that resolves the issue.

PS: I don't think we will need to but sharing is achieved through dropbox, google drive, onedrive or another sharing platform and you make it available to anyone with the link, then post the link here.

 

[knochel]

I need to do so more error checking, but replacing a "." with "_" looks to do the trick.

 

[Alex Blakenburg]

Sounds promising.
It was a good call to show me your Name Manager

 

[knochel]

Sounds promising.
It was a good call to show me your Name Manager

I appreciate your help!

 

[Alex Blakenburg]

No problem. Glad I could help and thanks for the feedback.