The truth is in here somewhere

I am in charge of a call centre, where staff work 24 x 7 each "day" starting from 0700 0nwards.
The staff can sit at any one of 21 desks each with its own job, each with its own unique identifierand I have to allow for meal breaks, training etc.

I would like to ascertain at the end of a period (monthly, weekly etc) how long a person has been at a particular desk (for developmental purposes) in hours.

The desk allocation table is split into hours (so 7 is 0700 x 0800hrs etc)

The name of the staff is not always in alphabetical or numerical order.

The desk allocation is input manually with one sheet per day.

I am wanting to know is there a way of having formulae in another sheet which would show what I require, and if so can you give me an example.

I have tried, count, countif, vlookup, hlookup in various guises to no avail.

Regards

Only way I would consider tackling that is to firstly consolidate all the sheets
into one using VBA (including a sheet identifier against each record, in this
case date), and then to throw a Pivot table at it. For an example of how to do
the consolidation:-

Sub SummaryCombineMultipleSheets()

Dim SumWks As Worksheet
Dim sd As Worksheet
Dim sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

HeadRow = InputBox("What row are the Sheet's data headers in?")
DataRow = HeadRow + 1

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set SumWks = Worksheets.Add

With SumWks
.Move Befo=Sheets(1)
.Name = "Summary Sheet"
Sheets(2).Rows(HeadRow).Copy .Range("1:1")
Columns("A:A").Insert Shift:=xlToRight
Range("A1").Value = "INDEX"
End With

With Sheets(2)
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
End With

For sht = 2 To ActiveWorkbook.Sheets.Count
Set sd = Sheets(sht)
lrow1 = SumWks.Cells(Rows.Count, "B").End(xlUp).Row
lrow2 = sd.Cells(Rows.Count, "B").End(xlUp).Row
sd.Activate
sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1,
2)
SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = sd.Name
Next sht

SumWks.Activate

End Sub

This way you can restrict the date range in the Pivot table query as well if you
want

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jeff Baker" wrote in message
...
I am in charge of a call centre, where staff work 24 x 7 each "day" starting
from 0700 0nwards.
The staff can sit at any one of 21 desks each with its own job, each with its
own unique identifierand I have to allow for meal breaks, training etc.

I would like to ascertain at the end of a period (monthly, weekly etc) how
long a person has been at a particular desk (for developmental purposes) in
hours.

The desk allocation table is split into hours (so 7 is 0700 x 0800hrs etc)

The name of the staff is not always in alphabetical or numerical order.

The desk allocation is input manually with one sheet per day.

I am wanting to know is there a way of having formulae in another sheet which
would show what I require, and if so can you give me an example.

I have tried, count, countif, vlookup, hlookup in various guises to no avail.

Regards


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004