Mac Excel 2011, Pivot Table from multiple workbooks

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
Please forgive if this has been covered, I searched but there were *so* many posts on the general topic and the first bunch I looked at were all Windows-only solutions, so I decided I just needed to post anew.

I'm on Mac Excel 2011, so there is no Pivot Table Wizard. I have 4 workbooks, each with an identical table (layout that is, data is of course unique in each) that I would like to summarize in a single pivot table in a different workbook.

Is it possible to create a pivot table in one workbook from data contained in tables in 4 other workbooks using Mac Excel 2011 and preferably without requiring VB, and if so... how would I go about this?

Thanks!
 
Wow that's awful. Thanks for the heads up. Fortunately I've finally learned, after 30 years of using a Mac, not to upgrade every time a new version of anything (including the OS) comes out. My last attempt was Yosemite and it went horribly wrong, thank goodness I had though to clone my boot HD before the upgrade. I'm still on Mavericks on all machines in the house (except one that is still on Lion!) and have no plans to change until something I absolutely must use, requires me to do so!

So all that being said... though I can now open the Wizard, I can't get it to allow me to select ranges from multiple workbooks. Every time I select a range from an open workbook, as soon as I hit the Add button, it strips the workbook out of the resulting reference and leaves me with only the worksheet name and range. So no way to know what book it came from.

I've read countless pages on this subject and every one of them either says to use Krill's VBA code (or some modification thereon) or some sort of 3rd party add-in, or manually merging all the data into a single table.

Is there a working set of instructions for how to actually get a single pivot table from multiple workbooks that each contain identically-formatted and named tables?
 
Upvote 0
WHEW. At long last, I got it. Had to manually type in my references as using the mouse to select the ranges was just not working properly, but in the end I got there.

Or so I thought. As I typed the above, Excel finally finished processing the tables and...

OUT OF MEMORY.

This on a machine with 32GB of RAM in it and basically nothing open but Excel and Chrome. I think Excel is now in a very bad state... probably going to have to relaunch and try again. Kinda doubtful this will ever work now. :(
 
Upvote 0
Well it took hours of trial & error and tons of googling, but I finally got what I wanted, more or less. I still don't really understand what's going on here, but I can see what I want in a single place now so I'm not gonna ask.

This is the tutorial that finally got me on the right track: http://www.contextures.com/xlPivot08.html

Perhaps it will help someone else one of these days too. Thanks starl for all your assistance!
 
Upvote 0
ANy suggestions how to do this on the Mac Excel 2016 - that is what I have and I desperately need to get Pivot Tables from multiple worksheets.
 
Upvote 0

Forum statistics

Threads
1,226,879
Messages
6,193,471
Members
453,802
Latest member
SarahNoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top