Reference a Table in another Workbook?

[dleseward]

I have a large workbook with many Excel Tables defined. I now want to split the workbook in 2 and have some of the Tables in the second workbook, while still using them in the first workbook. I want to preserve the ability to use structured references, etc. However, I don't see any way to set this up in Excel. I am using Excel 2010.

For example:

In Workbook1.xlsx
A1 = index(ExternalTable, match(row,ExternalTable[Index],0), match(column,ExternalTable[Column C],0)

Where ExternalTable maps to: [Workbook2.xlsx]TableSheet!$A$1:$C$10

I had thought that as the name "ExternalTable" is stored like any other Name in Excel, I could simply change the reference so that it pointed to the other workbook using the normal external reference syntax. This does not work, as the range for a Table definition is greyed out and can't be changed.

I then thought I should be able to Insert a Table and define the range as [Workbook2]TableSheet!$A$1:$C$10. If I try this, Excel complains saying the range for the Table has to be on the same sheet as as where I am Inserting it.

Reading through "Excel 2010 In Depth" I don't see any references to Tables in another workbook in the section on external references. So is this just not possible?

The only option would seem to be to put the tables in an Access database and then refer ot these from within Excel.

thanks,
David

 

[T. Valko]

The only option would seem to be to put the tables in an Access database and then refer ot these from within Excel.

If you use regular A1 referencing it'll work!

 

[dleseward]

I take it you mean, convert the Table references to regular Named range references.

I could, but then I'd have to edit a lot of formulas using rather more complicated references I think. I was hoping to avoid this.

thanks,
David