Dynamically link to selected columns of a table in a separate xls (external reference)?

[bw314]

I have a large table in one xls. (An 'excel table' and named ranges.) What is the best method to use a subset of this data in another spreadsheet? I have tried a number of methods and internet searches, but feel that I do not have the best solution.

Working:
- Match, index & vlookup using the external filename & named range ex. match(A1,'filename.xls!NamedRange,0)
- Pivot table based on the 'filename.xls!NamedRange, but then all of the data in the original large table is carried into the linked spreadsheet. (More than I need.)

Not working:
- Structured references to 'Excel table' changed to #REF! whenever the xls is recalculated (while the source is closed).
- Offset function that refers to cell addresses or named ranges gives the same problem.
- MS Query: Can browse to the xls but not the named range.
- Data Connections: Can browse to the xls & its named range, but I have not determined how to reference that connection in the spreadsheet formulas.

I do not want to:
1) Use direct cell reference because I want the connection to be robust (i.e. not break if a column is added to the original spreadsheet).
2) Require the source spreadsheet to be open when recalculating the destination worksheet.

Any suggestions? Can I use a query? Thanks in Advance!

 

[bw314]

Update: Despite the extra data, the Pivot Table is much more efficient (file size & speed) than the formulas.
Pivot Table:1 MB
Match/Index Formulas: 4.5 MB (Only 18 columns x 5600 rows, no special formatting. Surprised me. I deleted all excess blank rows & columns and save as new filename, but still big.)

For the Pivot Table, I can work around the Excel 2007 problem of blank (non-repeating labels) by including making the first column unique (ex. row numbers from the source table).