Table reference in formula disappears after workbook is closed

STGE2

New Member
Joined
Oct 8, 2009
Messages
17
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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 :-)
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,702
Members
453,748
Latest member
akhtarf3

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
Back
Top