Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
look for a value with multiple variables | Excel Discussion (Misc queries) | |||
Calculating on multiple variables | Excel Discussion (Misc queries) | |||
Sumif with multiple variables | Excel Discussion (Misc queries) | |||
Lookup (multiple variables) | Excel Worksheet Functions | |||
if function multiple variables | Excel Worksheet Functions |