Forum Discussion
Linking between workbooks
I have a collection of a dozen workbooks, all residing in the same directory, with many links between them. Some are very large, so large that placing all the content within a single workbook would ...
Thank you for your quick response on this, and sorry it took me so long to prepare a demo, but there were other issues I wanted to be sure were resolved before pursuing this further.
I will attach two workbooks, both stripped down to just one portion of one sheet, and each containing a structured table. Dump both into the same directory, and then try to open the file "accounts demo.xlsx" first. You will see it tries to pull data from "projects demo.xlsx". Even if you update the path to the file, you will note it does not properly pull in this data without also opening the file "projects demo.xlsx". If you try to simply use Queries & Connections / Edit Links / Update Values, you will get an "OK" response, but the values will not update.
The goal is managing a system of two dozen interconnected workbooks, some of which are very large and contain too much data to reasonably keep them all opening while editing just one file. I am looking for the best system to make this work, if I have chosen a poor path, here.
I will attach two workbooks, both stripped down to just one portion of one sheet, and each containing a structured table. Dump both into the same directory, and then try to open the file "accounts demo.xlsx" first. You will see it tries to pull data from "projects demo.xlsx". Even if you update the path to the file, you will note it does not properly pull in this data without also opening the file "projects demo.xlsx". If you try to simply use Queries & Connections / Edit Links / Update Values, you will get an "OK" response, but the values will not update.
The goal is managing a system of two dozen interconnected workbooks, some of which are very large and contain too much data to reasonably keep them all opening while editing just one file. I am looking for the best system to make this work, if I have chosen a poor path, here.
ThomasRA4 I can confirm your findings.
Looks like it does work if you name each column of the projects table and refer to the range names. See attached.
This appears to work if you use fixed range references only (eg. $A$2:$A$99), which explains why all my prior attempts had failed. I've been trying to use various methods of dynamic ranging, most recently leveraging on the structured table to define the column name. All such dynamic range methods seem to fail, but the good news is that by employing structured tables, the fixed range defined for a column (if same as table range) seems to automatically expand with the table.
Thanks!
