If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
The truth is in here somewhere
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 |
Thread Tools | |
Display Modes | |
|
|