Multiple files as Pivot source.

[jei eusebio]

Hi Ninjas,

I have multiple files that have reports with the same formats, is it possible to create a Pivot Table on a different Excel file and make those multiple files as the source data?? and how do I do that? lol .. thanks in advance! :D

 

[zequla]

Hi,

Yes that is posibble, go to insert -> pivottable - > under choose the data go to under an external data source -> click on choose connection -> at the bottom left choose browse for more -> select your excel file with the reports in in-> choose the sheet and done

greetzz

 

[jei eusebio]

thanks a lot zequla!

 

[zequla]

No problem. have a nice day =]

 

[jeffreyweir]

If you have multiple files then I don't understand how that's going to help you...that creates a connection to one file only....something you could just as easily do without using that external data route.

Rather, some options are:

• Use the code approach outlined here: http://blog.contextures.com/archives/2010/08/30/macro-creates-excel-pivot-table-from-multiple-files/
• Install the Power Query addin. See http://www.microsoft.com/en-nz/download/details.aspx?id=39379

 

[jei eusebio]

yes , I tried it and it only connects to 1 file. I think the first link is the one I'm actually looking for. Thanks jeffreyweir!