Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating what staff worked on which product
Hi,
i have an excel file that is manually updated and calculated that marks what product our software developers have worked on during the day what this file, has is different worksheets for each month (so each file has 12 worksheets as each one is represented by each month), and within each month - is a list of staff names going vertically in the first column - each day of that specific month going horizontally in the first row - on each day we mark a reference code that enables us to determine which product a certain staff member worked on which then at the end of every financial year we can determine the overall cost of what each product has costed us for the year, but this is manual calculation by going through each worksheet and marking the count somehow, we would like to automate the calculation of the amount of times a staff member works on a product for the past year i am struggling with which function i should be looking at? its been a while since i have done smething this complicated in excel and my knowledge isnt where it used to be. hopefully some help is provided, it would be appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating what staff worked on which product
DumDum was thinking very hard :
Hi, i have an excel file that is manually updated and calculated that marks what product our software developers have worked on during the day what this file, has is different worksheets for each month (so each file has 12 worksheets as each one is represented by each month), and within each month - is a list of staff names going vertically in the first column - each day of that specific month going horizontally in the first row - on each day we mark a reference code that enables us to determine which product a certain staff member worked on which then at the end of every financial year we can determine the overall cost of what each product has costed us for the year, but this is manual calculation by going through each worksheet and marking the count somehow, we would like to automate the calculation of the amount of times a staff member works on a product for the past year i am struggling with which function i should be looking at? its been a while since i have done smething this complicated in excel and my knowledge isnt where it used to be. hopefully some help is provided, it would be appreciated Why not simply SUM the columns for each month and insert a summary sheet to collect the total of each product? (This assumes the product list is identical on every sheet including the summary) Otherwise, more info is required to know exactly how staff updates the sheets. Do they 'clock' on/off each work instance or use formula-style entry (ie: "=<timeworked+<timeworked...)? Do you want to summarize by product, staff, or both? (We aren't mind readers!) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
|
|||
|
|||
This is separate to the timesheets
this is just a manual process, where the managers of each team get together and say, This person worked on this product and that product for the past week i want to summarise by Staff and Product Example of the output im looking at, in best i can in this format Product 1 Product 2 Sick Leave Leave Staff 1 250 45 4 1 Staff 2 80 200 1 20 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating what staff worked on which product
DumDum was thinking very hard :
This is separate to the timesheets this is just a manual process, where the managers of each team get together and say, This person worked on this product and that product for the past week i want to summarise by Staff and Product Example of the output im looking at, in best i can in this format Product 1 Product 2 Sick Leave Leave Staff 1 250 45 4 1 Staff 2 80 200 1 20 Ok, you'll have to add a summary sheet to consolidate the 12 month sheets. Each sheet's staff list and product list should be identical column-for-column, row-for-row.(all 13 sheets) This means staff will have to use formula style entry for multiple work sessions under any one product. If your sheets are used another way or their layout is different then the follow solution won't work. Create a local-scope defined name range on each of the 12 month sheets named "DataArea" as follows: Select a month sheet; Select all the data columns/rows (except totals at the bottom/right if any) Open the Define Name dialog; In the name box type the name like this... '<sheet name'!DataArea ..where you need to substitute <sheet name with the actual sheet name; Press the Enter key and move on to the next month sheet and repeat the process for all 12 sheets. Make the summary sheet the active sheet and open the Define Name dialog. Create 12 defined name formulas as follows: Name: '<sheet name'!TotalWks1 ..where you need to increment the numeric suffix by 1 for each of the 12 month sheets, resulting in 12 defined name formulas of "TotalWks1" through "TotalWks12", AND you need to substitute <sheet name for the actual sheet name of the summary sheet. RefersTo: =INDEX('Sheet1'!DataArea,ROW(),COLUMN()) ..where you need to substitute Sheet1 for the actual sheet name of each of the 12 month sheets. ...do all 12 names before the next step! Select all the cells to receive totals from the month sheets. This will start in B2 and end in the row with the last staff name, all the way to the last product column. Type (or paste while in EditMode) the following formula... =SUM(TotalWks1,TotalWks2,TotalWks3,TotalWks4,Total Wks5,TotalWks6,TotalWks7,TotalWks8,TotalWks9,Total Wks10,TotalWks11,TotalWks12) ~~Note that if pasting, the above formula is all one line!~~ Hold down the Ctrl key and press the Enter key to enter the formula into all selected cells at once. Optionally, you could create another defined name formuala like this... Name: '<sheet name'!YearTotal ...and put the above formula in the RefersTo box. Then the cells formula could be =YearTotal which would be more neat and more self-explanatory than the longer one. Now you can place a totals row at the bottom and a totals column to the right of the product columns to SUM by product/staff. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet needed to total weekly hours worked by staff | Excel Discussion (Misc queries) | |||
Calculating Time Worked in Timesheet | Excel Worksheet Functions | |||
Calculating the number of days worked | Excel Worksheet Functions | |||
Calculating Pay based on time of day hrs worked | Excel Worksheet Functions | |||
calculating weeks worked | Excel Worksheet Functions |