Update data by an external reference (link) to a table in another workbook

Question

I have defined a table Table1 in source file.xlsx. When source file.xlsx is open, in target file.xlsx, I could use ='source file.xlsx'!Table1[#Data] in a cell to get the table data.

However, I realise that if I close source file.xlsx, that formula becomes ='\\Mac\Home\Downloads\source file.xlsx'!Table1[#Data] and its value becomes #REF!.

This is not the case for an external reference (link) to a cell range in another workbook; an external reference to a cell/range could still get value when the source file is closed.

Could anyone tell me how to update the table data even though when the source file is closed?

Answer 1

To answer your question:

What you are seeing is how excel works at a cell formula level. If you type in =[file.xlsx]Sheet1!Table1[#All] This might be a little more of what you want, but it will give you the whole table. For one column you can do:

=file.xlsx!Table1[[#Headers],[Date]]
=file.xlsx!Table1[Date]

You can create a defined name for Table1 as mytable then you would enter the formula =file.xlsx!mytable

All the above becomes similar to ='\\Mac\Home\Downloads\source file.xlsx'!Table1[#Data]

For an alternate method (recommended):

The very best way I have found to do this is using power query. This is not formulas in cells but it is like a database query tool. There are several people on You-tube making videos about using power query for this exact thing. I will step you through the process starting in the file where you want the data to appear. Go to:

1. Data > (Get & trasform Data) Get Data> From File> From Workbook...
2. In the popup window navigate to your file "\Mac\Home\Downloads\source file.xlsx" and click Import.
3. In the next window choose "Table1" (or an item that you want).
4. Now choose load and this data loads into your sheet.

If you want to transform the data of "Table1" then...

1. Data > Queries & Connections> (this pops open a "Queries & Connections")
2. Right click on Table1 then edit.
3. There are many options to choose from such as: remove columns, filter...
4. To calculate new columns use > Add Column which uses M function.
5. For Vlookup use Merge Queries,
6. For a Count, Sum, Average, or ... of rows (utilizing one or multiple columns) use "Group by".

After you "Close & Load" the table gets updated. If new values get entered into file.xlsx Table1, refresh the power query table and the data shows up.

Answer 2

This is expected behaviour

From the Microsoft Help

Links to data tables residing in external files display #REF! Symptoms Consider the following scenario:

1. You insert a table in an Excel file, named for example source.xlsx.

2. In another file, named for example destination.xlsx, you insert a link to the data table which resides in source.xlsx.

3. You close both source.xlsx and destination.xlsx.

4. You open destination.xlsx and you choose to update links when prompted.

Result: the cells that are linked to the external data table display #REF!.

Cause This is expected behavior for Excel.

New references to external workbooks that aren’t open will successfully parse without verifying the reference, but will return #REF.

When loading an external structure reference to a closed workbook, the reference is not updated by default. If you choose to calculate the formula the result is #REF! instead of the corresponding value.

Microsoft Help

As an alternative, create a query to the table. This can be refreshed from a closed workbook