Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ang Ang is offline
external usenet poster
 
Posts: 36
Default 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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 149
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ang Ang is offline
external usenet poster
 
Posts: 36
Default 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!




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 149
Default 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!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
look for a value with multiple variables Andrea Excel Discussion (Misc queries) 2 January 18th 07 01:24 PM
Calculating on multiple variables DubboPete Excel Discussion (Misc queries) 1 April 19th 06 05:38 AM
Sumif with multiple variables les8 Excel Discussion (Misc queries) 5 April 8th 06 02:16 AM
Lookup (multiple variables) stevenpwhite Excel Worksheet Functions 2 December 16th 05 12:03 PM
if function multiple variables cin Excel Worksheet Functions 2 September 8th 05 09:32 PM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"