Use structured references from separate workbooks?

[kurtkaufman]

Hello,

I'm working on simplifying the process of consolidating sales data. Each division (let's say A,B, and C) sells products through dealers. All of the divisions sell through the same dealers, but the dealers are always changing monthly (being added or removed).

Each month I send out templates to the divisions with updated dealers (this is done manually by adding and removing rows). Then I take all of the templates, and their numbers are pulled into a consolidation file. Whenever rows are added or deleted, I have to go into the consolidation file and change the referenced rows.

I wanted to make all of the templates into tables, and then use structured referencing to quickly and easily consolidate data into the consolidation file, but I'm having trouble.

This is what I tried, but could not get it to work...

=sum('[A.xlsx]2017'!TableA[Full Year],'[B.xlsx]2017'!TableB[Full Year]...) and so on

Suggestions would be greatly appreciated!

 

[T. Valko]

I don't think you can use structured referencing on external files.

 

[kurtkaufman]

Unfortunately, that's the conclusion I've come to as well... Any potential alternative solutions are welcome!

 

[T. Valko]

Unfortunately, that's the conclusion I've come to as well... Any potential alternative solutions are welcome!

Don't use structured references!

 

[FDibbins]

Don't use structured references!

Not a huge fan either.

(can you send me a PM please?)

 

[T. Valko]

can you send me a PM please?

I've temporarily enabled PM's.

 

[FDibbins]

I've temporarily enabled PM's.

Thanks