Calculating what staff worked on which product

[DumDum]

Hi,

i have an excel file that is manually updated and calculated that mark
what product our software developers have worked on during the day

what this file, has is different worksheets for each month (so each fil
has 12 worksheets as each one is represented by each month), and withi
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 determin
which product a certain staff member worked on which

then at the end of every financial year we can determine the overal
cost of what each product has costed us for the year, but this is manua
calculation by going through each worksheet and marking the count

somehow, we would like to automate the calculation of the amount o
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
while since i have done smething this complicated in excel and m
knowledge isnt where it used to be.

hopefully some help is provided, it would be appreciate

 

[GS]

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!)

 

[DumDum]

This is separate to the timesheets

this is just a manual process, where the managers of each team ge
together and say, This person worked on this product and that produc
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
2

 

[GS]

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,TotalWks5,TotalWks6,TotalWks7,TotalWks8,TotalWks9,TotalWks10,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