How to Create a Summary of Workbook

[MGC]

I have a major workbook with several tabs, each tab is a timesheet for a
particular day a job is worked. A purchase order has been given to me for
this job and I am trying to create a spreadsheet on the first tab in the
workbook which will keep track of the amount remaining on the PO for this
particular job. My problem is this: How can I get the hours worked and the
job classification to automatically roll onto the first tab without too much
human intervention? The timesheets will vary from day to day as the number
of people utilized will change daily. Is there a way this can be done?

 

[Roger Govier]

Hi

Having data spread across multiple tabs makes life more difficult, and
without detailed knowledge of your layout it is impossible to answer.

If you copy all of the daily data to a single sheet, adding an extra column
to hold the date (As all data will now be on one sheet), then you could
create a Pivot Table to summarise the data.
Essentially there are only 2 columns (fields) that are required for the
summary, but you could incorporate any more that you wish for analysis.
On your new sheet of consolidated data, create a Dynamic range for your
source data for the Pivot Table.
Insert>Name>define>Name Mydata Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Place you cursor on the top row of data>Data>Pivot Table>Next>Source
=Mydata>Finish
On the newly created sheet with a Pivot table skeleton,
Drag the field for Order Number to the Row Area
Drag the field for Hours worked to the Data area

On your Summary tab, set up 4 headings Order Number, Hours Booked, Hours
Worked, Hours Remaining
Enter the actual hours booked in column B
In C2 enter
=GETPIVOTDATA("Hours",Sheet4!$A$3,"Job",A2)

(Change the Sheet reference above, if your PT is not on Sheet4)
Copy down the column
In D2
=B2-C2 copied down

For more help on Pivot Tables, take a look at
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
or
http://www.datapigtechnologies.com/ExcelMain.htm