Combine Pivot tables - confused about 'how-to'

H

heyredone

I have read several instructions on-line & here on combining pivot table data
and am confused and/or I'm doing it wrong. Anyone's help would be sincerely
appreciated! Using Excel 2003.

I have pivot tables of employee time punch data and more than/less than
number of hours worked per day calculations. 1 pivot table worksheet = 1
month; 1 workbook = 12 months (Jan-Dec); there are 5 workbooks (2004-2008). I
want to combine pivot table data for months Jan-Dec into 1 pivot table. Can I
do that? If so, how?

Data labels are "Last Name", "First Name", "Badge", "More than 3.5 hrs?",
"More than 5 hrs?", "More than 10 hrs?", "Less than 12 hrs?" etc. with the
"More than" & "Less than" as 'True'/'False' or 'n/a' results. Goal is to see
over the course of 1 year, the count of 'True' and 'False' and 'n/a' results
for each employee. I will then need to combine over the course of 2004-2008,
the same count results for each employee. I'll start with info. per year for
now, though.

I hope this makes sense. Need more information, please ask. Thank you very
much!
 
A

Ashish Mathur

Hi,

I was trying your solution but could not quite get your result. When I
select the range and click on 1 (for I will select 1 page field), I do not
see any any option in the drop down there. Could you please elaborate on
the process.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
H

Herbert Seidenberg

Ashish,
In step 2b of the PivotTable Wizard,
select the number of page fields (i.e. 1),
select a range in the "All ranges" window,
(i.e. Table5[[#All],[Badge]:[Less12]])
and type in an arbitrary label into "Field one",
(i.e. May).
After typing in the months for all the ranges,
click on the arrow and all the months will show,
(for no practical purpose)
 
A

Ashish Mathur

Hi,

Thank you for the solution. I still cannot get it right. I have taken
another example at the following link -

http://cid-54480ad6bae0067b.skydrive.live.com/self.aspx/Public/multiple sheet pivot.xls

My question is - Why does "Sector" and "Department" (which are non-numeric
fields) appear in the data area? I want to show it (as a text filed) in the
row area or column area. I'd appreciate if you could elaborate the steps.

Thank you for your time and help.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top