Multiple variables-SOS
 

I need your expert help in setting this up. I'm building a labor report
that I want to pull data into from another spreadsheet.

Spreadsheet B contains date, department name, hours worked, and dollars by
employee that worked that day. I want to summarize by day in my report:
Department Total Hours Toal $

What's the best, easiest way to do this? Use in intermediate spreadsheet
and set up an array? I've tried sumproduct and for some reason it just
doesn't work...HELP?
THX MUCH!

Multiple variables-SOS
 

Best & easiest? Pivot table.

- Data"Pivot table and chart report"
- Step1 screen: Excel Data, Pivot table
- Step2 screen: Select the data from sheet B that you want summarized
- Step3 screen: tell Excel where you want the pivot table
Then:
- Add Day and Dept to Row Area (in that order)
- Add Hours and Dollars to Data area

Hours and Dollars will probably be "stacked" (separate rows) rather than
"spread" (separate columns). To make them appear in separate columns, click
& drag the gray "Data" marker up & to the right just a little bit to "Pivot"
those fields.

If you don't want to see subtotals for each day, right click on Day, Field
Settings..., then select "None" for Subtotals.

To remove the "Sum of.." from the Data field names, right click on the
field, Field Settings..., then change the name (it can't be exactly what it
was in the source data but you can add a space at the end to make excel
happy if necessary). From this dialog you can also select "Number..." to
apply number formatting. You can also select a different subtotal type to
Count, Max, Average, etc., if you wanted to.

If you want to see the data grouped by Department, then by Day, click & drag
Dept so that it is to the left of Day.

HTH,


"Ang" wrote in message
...
I need your expert help in setting this up. I'm building a labor report
that I want to pull data into from another spreadsheet.

Spreadsheet B contains date, department name, hours worked, and dollars by
employee that worked that day. I want to summarize by day in my report:
Department Total Hours Toal $

What's the best, easiest way to do this? Use in intermediate spreadsheet
and set up an array? I've tried sumproduct and for some reason it just
doesn't work...HELP?
THX MUCH!

Multiple variables-SOS
 

Thx George - I'll try this. To add to the mix, though, I have another number
I want to add to the result of all of this. For example, what you gave me I
could use for hourly employees. But now I want to add in the related
departments' salaried employee wages and hours. Can I do this adding to a
pivot table?

I've been scratching my head for weeks on how to do this report! Any help
would be greatly appreciated!
-Ang

"George Nicholson" wrote:

Best & easiest? Pivot table.

- Data"Pivot table and chart report"
- Step1 screen: Excel Data, Pivot table
- Step2 screen: Select the data from sheet B that you want summarized
- Step3 screen: tell Excel where you want the pivot table
Then:
- Add Day and Dept to Row Area (in that order)
- Add Hours and Dollars to Data area

Hours and Dollars will probably be "stacked" (separate rows) rather than
"spread" (separate columns). To make them appear in separate columns, click
& drag the gray "Data" marker up & to the right just a little bit to "Pivot"
those fields.

If you don't want to see subtotals for each day, right click on Day, Field
Settings..., then select "None" for Subtotals.

To remove the "Sum of.." from the Data field names, right click on the
field, Field Settings..., then change the name (it can't be exactly what it
was in the source data but you can add a space at the end to make excel
happy if necessary). From this dialog you can also select "Number..." to
apply number formatting. You can also select a different subtotal type to
Count, Max, Average, etc., if you wanted to.

If you want to see the data grouped by Department, then by Day, click & drag
Dept so that it is to the left of Day.

HTH,


"Ang" wrote in message
...
I need your expert help in setting this up. I'm building a labor report
that I want to pull data into from another spreadsheet.

Spreadsheet B contains date, department name, hours worked, and dollars by
employee that worked that day. I want to summarize by day in my report:
Department Total Hours Toal $

What's the best, easiest way to do this? Use in intermediate spreadsheet
and set up an array? I've tried sumproduct and for some reason it just
doesn't work...HELP?
THX MUCH!

Multiple variables-SOS
 

Hard to say. Pivot tables are great to summarize the data in a single
contiguous block of data. If the additional data is in another table then a
single pivot may not be the answer.

On the other hand, *if* you could create a 2nd pivot on the Salaried data
that has the same column layout as your Hourly pivot, you could right-click
on those 2 tables, Select..Entire table, Copy, PasteSpecial..Values (to
another location), delete header rows, etc.

This would give you a table that includes data from both Hourly and Salaried
employees. you could then do a single pivot off of the combined data.

If you go that route, consider adding a "EmpType" field to the raw data (and
the interim pivots) so you can still summarize Hourly and Salaried
separately on demand even after you've "merged" their data for the final
pivot.

HTH,


"Ang" wrote in message
...
Thx George - I'll try this. To add to the mix, though, I have another
number
I want to add to the result of all of this. For example, what you gave me
I
could use for hourly employees. But now I want to add in the related
departments' salaried employee wages and hours. Can I do this adding to a
pivot table?

I've been scratching my head for weeks on how to do this report! Any
help
would be greatly appreciated!
-Ang

"George Nicholson" wrote:

Best & easiest? Pivot table.

- Data"Pivot table and chart report"
- Step1 screen: Excel Data, Pivot table
- Step2 screen: Select the data from sheet B that you want summarized
- Step3 screen: tell Excel where you want the pivot table
Then:
- Add Day and Dept to Row Area (in that order)
- Add Hours and Dollars to Data area

Hours and Dollars will probably be "stacked" (separate rows) rather than
"spread" (separate columns). To make them appear in separate columns,
click
& drag the gray "Data" marker up & to the right just a little bit to
"Pivot"
those fields.

If you don't want to see subtotals for each day, right click on Day,
Field
Settings..., then select "None" for Subtotals.

To remove the "Sum of.." from the Data field names, right click on the
field, Field Settings..., then change the name (it can't be exactly what
it
was in the source data but you can add a space at the end to make excel
happy if necessary). From this dialog you can also select "Number..." to
apply number formatting. You can also select a different subtotal type
to
Count, Max, Average, etc., if you wanted to.

If you want to see the data grouped by Department, then by Day, click &
drag
Dept so that it is to the left of Day.

HTH,


"Ang" wrote in message
...
I need your expert help in setting this up. I'm building a labor report
that I want to pull data into from another spreadsheet.

Spreadsheet B contains date, department name, hours worked, and dollars
by
employee that worked that day. I want to summarize by day in my
report:
Department Total Hours Toal $

What's the best, easiest way to do this? Use in intermediate
spreadsheet
and set up an array? I've tried sumproduct and for some reason it just
doesn't work...HELP?
THX MUCH!