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.

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

<tbody>
</tbody>

 

[claybwagner28]

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