Table from other file as a source for pivot table

mrMadCat

New Member
Joined
Jun 8, 2016
Messages
35
Hi people!
I have data in separate file organized as excel table range. I want to use this table name as a source for pivot table in other file. The funny thing it doesn't let me do this saying the range is wrong. Though it works ok inside 1 file.
Additionally, trying to come around, when I change range to just A:X it lets me select it but pressing OK doesn't close the pivot table source window. It just doesn't react. So finally I can't use that data anyway.
Any thoughts? :confused:
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mart37

Active Member
Joined
Aug 4, 2017
Messages
398
Office Version
  1. 2016
Platform
  1. Windows
Did you enter something like: ='sourcefile.xlsb'!Table1[#All]
 

mart37

Active Member
Joined
Aug 4, 2017
Messages
398
Office Version
  1. 2016
Platform
  1. Windows
easy way:
put in cel '=' and select in the sourcefile the whole table and puss ENTER
now you have the complete addressrange for your pivot table
copy everthing behind the '='
paste in diaologbox
 

mrMadCat

New Member
Joined
Jun 8, 2016
Messages
35
Thank you for trying to help. The problem was that trying to turn off all the alerts and auto turn on macros I turned off all external source connections. So pressing OK in the sources makes no action.
Now all is ok but the sad thing is that Pivot table works only when source is open. If it is closed excel asks to open it first. So it looks like this "table ranges" are still not a very functional thing with many bugs and limitations.
 

mart37

Active Member
Joined
Aug 4, 2017
Messages
398
Office Version
  1. 2016
Platform
  1. Windows
Stay calm. Table Range are the future.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,476
Messages
5,572,340
Members
412,458
Latest member
melanau
Top