Pivot tables - Excel 2007

Pivot tables - Excel 2007

Didn't find your answer?

I have managed to produce some very useful pivot tables based on timesheet information. The source data is in the following columns: Job number, Job name, Staff name, Hours worked, Month, Hourly rate, Cost (hours worked x hourly rate). This enables me to pull off information very quickly for management to report on hours and/or cost per job, employee etc in various combinations on an ad hoc basis.

This is fine when the managers just want a printed report, but I'm struggling with the best way to email individual reports to them. Is there a way of being able to filter the pivot table to show the criteria needed, and then copy the result into a new worksheet without the source data, whilst converting the data to values but not losing the format of the pivot table? When I try to copy, I lose the the formatting of the table.

Thank you in advance for any help.

Claire

Replies (1)

Please login or register to join the discussion.

avatar
By mikeopolo
08th May 2008 20:47

Keep your source data in a separate file
I suggest you keep your data in a separate worksheet, and create pivot tables in an/other workbook/s.

To do this, give your timesheet data a range name, and save it separately.

Then when creating a pivot table, choose Excel list or database as usual, and in step 2, instead of choosing the range, browse to select the data file. Your selection will end in a '!'; add the range name to the end of this text and you will then have access to your timesheet data.

The workbook containing the pivot table can be emailed directly from Excel (File, Send To,Mail Recipient (As attachment).

My instructions are for Excel 2003, which is all I have, so the actual steps may differ for XL 2007.

hth
Regards
Mike

Thanks (0)