Table reference in formula disappears after workbook is closed

[STGE2]

Hi

I have been googling and googling for a long time now but cannot find any other with the same issue as me.

I have a table (tCableData) with a lot of columns. I'm then doing some lookups in this table to use data from this table on other sheets.

I insert a formula like this (in another worksheet than where the table is stored.. don't know if this make any difference):

=INDEX(tCableData[CableType];MATCH(C9;tCableData[Crosssection];0))

Now I save the workbook and close it. When I open the workbook again excel has changed my formula to the below:

=INDEX('Cable list'!$H$8:$H$227;MATCH(C9;'Cable list'!$I$8:$I$227;0))

This is very annoying for me and I cannot figure out why Excel does this?

The only solution I see it to put a formula in a named range to fix the problem but that will be a LOT of named ranges to create and control... it would be a lot easier to handle through table references.

Best regards
Stine

 

[STGE2]

Typical I have spend an our on this now and just after I write this thread I find the solution

I will share it here if anyone have the same issue.

The problem was of cause the format of the excel file! The file was saved as .xls and therefore could not handle table reference. When I saved the file as .xlsm (or .xlsx) there were no issue and the table references were all fine