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