Structured Reference does not auto complete

[deekaybee]

When I select a cell outside of the table and I try to reference a cell inside of the table by typing an equal sign and then clicking a cell inside of the table I don't get a structured reference; I get a (for example) A1 the column row reference. Use table names are turned on in Excel options/Formulas/Working with formulas. Enable AutoComplete for cell values are also selected in Excel Options/Advanced/Editing Options. Even when I manually type the structured references in, if I close and reopen the spreadsheet, the references will change to a sheet name colomn row. Example: Sheet1!$A1. It use to work and as far as I know no changes were made to the computer other than network pushed updates. Thanks

 

[RoryA]

Which version of Excel - 2007 or 2010?

 

[deekaybee]

Which version of Excel - 2007 or 2010?

I'm using version 2007 (12.0.6557.5000) SP2 MSO (12.0.6554.5001)

 

[RoryA]

Is your file in xls format by any chance?

 

[deekaybee]

I have one version of it in .xls .xlsm and .xlsx

 

[RoryA]

The xls version would behave the way you describe. The others should not.

 

[deekaybee]

Should not is the operative phrase. It shouldn't but it does. I'm at a total loss as to why. I seen others with the same problem on other boards and nobody that I could find has posted a fix yet. I hope you'll have better success. Thanks

 

[deekaybee]

I think I figured it out. When working in a .xls version of the sheet, it didn't work. I then saved the file (not created a new one) in .xlsm format and it still didn't work. Then I tried it in .xlsx format and still no luck. But when I copied the data and pasted it to a separte sheet (still in same workbook) it worked. I imagine some of the formating or rules stayed with the table even though the saved version (i.e. file extension) changed. Thanks for all your help.