Excel 2007 : External table reference throws invalid cell reference unless source document is open

I have a workbook with a table of names.
I have another workbook with another table, one column of which I wish to populate with the names from the first workbook.

If I reference the table name/column of the source workbook:
If both workbooks are open, everything works perfectly fine. If I only open the secondary workbook, all of the references are what they should be but show an inconsistent calculated column error. If I update the values via the trust bar or links dialog, all of the references throw invalid cell reference errors.

If I reference the cell location (IE $B2) of the source workbook:
The secondary workbook populates and updates fine, but the links dialog box shows "open source to update links".

It appears that I may just have to do cell references as opposed to the table references, but I'd like to do table references for consistency's sake. Is there any way to achieve this?

Thank you for taking the time to view my question and in advance for any assistance.

Originally Posted by shg

Welcome to the forum.

What formula are you using?

Thank you for the welcome.


The reference to the source is

Please Login or Register  to view this content.

That's when the source is open

When the source is not open:

Please Login or Register  to view this content.

Same issue happens if I reference Table[[#This Row],[Name]]


Now, if I reference

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

it seems to work alright.


Again, the table references work fine when the source is open, but errors when it isn't. The cell references seem to work alright regardless of whether the source is open or not.

The names of users, sheets, and tables have been changed to protect the innocent.