Pivot Table combining several tabs

[sstorm]

Good Afternoon. I am attempting to create a pivot table using 4 columns from 5 different tabs. I have created pivot tables before using one tab and they are pretty easy. However, I have been working on this for over an hour and I am getting nowhere. Can someone tell me how to put all five tabs into one pivot table? The columns are all in the same order on each tab. They are as follows: System, Management Area, Schedule Date, WO Type. I want the pivot table to have System and Management area on the left side, date on the top row and counting the number of WO Types. Any ideas? Thanks!

Stac

 

[BobUmlas]

In step 1 of the wizard, select Multiple consolidation ranges, then follow the prompts.

 

[sstorm]

I did that...I can't get it to set up the way that I want to.

 

[BobUmlas]

send me a sample file offlist to bobumlas@yahoo.com

 

[sdurocher]

This might help you, but it involves Access.

You can link those tabs into Access via the link table option. This will keep the data uptodate (once the Excel spreadhsheet is saved the database is updated).

Then create a Union Query. Example;

UNION ALL
SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2
UNION ALL
SELECT *
FROM Table3
UNION ALL Select *
FROM Table4;

Then make a query of the union query. Excel can't read a Union queries code, so you have to put into a regular query form.

Then link your pivot table to the Access query. All of your data will now be under one header.

 

[Greg Truby]

Sean, interesting idea. I'll keep that in my pocket should I ever need it.

Bob, (a) welcome, I'd seen your profile in Colo's Cell Masters; hadn't seen too many posts from you 'round these parts, (b) and maybe I'm just paranoid, but I generally advise folks that post an e-mail addy to replace the @ with text or something like: first.last[at]domain.com so that the dread 'bots don't grab yer addy and spam ya into an asylum.

 

[BobUmlas]

Hi, Greg, thanks for the heads up re "@" -- I'm not paranoid, but maybe I will change it!
I usually hang out at PEACH-G&L lists, or Microsoft, or AllExperts. I'll be here more often!