One Pivot Table for 19 worksheets

[claybwagner28]

Ok here is my dilema. I need to track progress for an annual certification program for my company. Each area has its own spreadsheet that is identical in setup. I want to create one pivot table for all the spreadsheets.

1) Do I merge/consolidate 19 worksheets into one worksheet and create from there? If so how?

Currently they feed information which I have linked to my workbook. When their spreadsheet updates, mine does.

2) Create pivot table using 19 spreadsheets? If so how?

I tried to researched onlione but became more confused. I have never done VBA macro. Is that hard?

Thank you all in advance for your help!

Clay

 

[Bmontema]

You could combine the data. just make sure that there is a field to identify which of the 19 that record belongs to. I would also suggest after combining, convert the data range to a table then make the pivot. That way, as rows of data are added, there will be no need to modify the data source. Because it is a table, it will extend automatically. You just need to refresh after adding data.

 

[claybwagner28]

How would I combine them? Sorry excel noob.

 

[claybwagner28]

Here is the sample data: Some data is text. Some numbers: Names changed of course. 19 worksheets like this in one workbook.

[TABLE="width: 1277"]
<tbody>[TR]
[TD]User ID
[/TD]
[TD]User Name
[/TD]
[TD]Level
[/TD]
[TD]CSM Direct Report
[/TD]
[TD]IPE Completion
[/TD]
[TD]Date of IPE
[/TD]
[TD] Manager Certification Pair
[/TD]
[TD]Rapport Score
[/TD]
[TD]Transistioned to Conversation
[/TD]
[TD]Greet Total Points
[/TD]
[/TR]
[TR]
[TD]W745G
[/TD]
[TD]Adams, DAN
[/TD]
[TD]BM
[/TD]
[TD]Manager 1
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]W9345G
[/TD]
[TD]Acorn, LESLIE
[/TD]
[TD]ABM
[/TD]
[TD]Manager 2
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]JMA4070
[/TD]
[TD]Aanybody, JENA
[/TD]
[TD]BM
[/TD]
[TD]Manager 1
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]GHkK40
[/TD]
[TD]ANDERSON, KEN N
[/TD]
[TD]ABM
[/TD]
[TD]Manager 3
[/TD]
[TD]Yes
[/TD]
[TD="align: right"]41934
[/TD]
[TD] Snell & Robert
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]LKK460
[/TD]
[TD]Available, LINDA
[/TD]
[TD]Platform
[/TD]
[TD]Manager 2
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]LYKO90
[/TD]
[TD]Awesome, Linda
[/TD]
[TD]BM
[/TD]
[TD]Manager 5
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]MKL090
[/TD]
[TD]BAKEttE, JOAN
[/TD]
[TD]ABM
[/TD]
[TD]Manager 2
[/TD]
[TD]Yes
[/TD]
[TD="align: right"]41887
[/TD]
[TD] Jordan Gamet
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]BLMN45
[/TD]
[TD]Bakersfield, KAREN
[/TD]
[TD]Platform
[/TD]
[TD]Manager 3
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]45690G
[/TD]
[TD]Bubba, DEBBY
[/TD]
[TD]Platform
[/TD]
[TD]Manager 4
[/TD]
[TD]Yes
[/TD]
[TD="align: right"]41920
[/TD]
[TD] Jordan Gamet
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
</tbody>[/TABLE]

 

[claybwagner28]

Is there a difference between combine and consolidate? Would it be easier to convert the 19 areas to tables first?