Solved

Macro Report based on timesheet entry non-compliance

Posted on 2014-04-02
15
566 Views
Last Modified: 2014-04-07
I have attached a spreadsheet that details all employees that have entered a timesheet for each day for the week commencing 24th March on the 'AllData' tab. I have also listed the employees that belong to a particular department on the 'Staff_Details' tab.

My objective is to achieve the following:

1. Identify those staff in the department stated on the 'Staff_Details' tab who simply have not entered a time sheet at all and list those days where a time sheet has been missed for each employee


2. Identify in a separate list, those employees who have incorrectly entered a timesheet (wrong days, number of hours for the week, number of hours for a particular day)


Rules include the following:

a. the total hours entered for each week (mon to saturday) for each employee does NOT equal or exceed the total expected hours in Col C of the 'Staff_Details' sheet
b. the hours entered for each employee on a particular day does not equal days on which each employee should not work as detailed in columns D to J of the 'Staff_Details' sheet
c. the hours entered for each employee on a particular day is not correct (i.e. less than the stated total  weekly hours in Col C of the 'Staff_Details' tab divided by the number of days that they are expected to work as detailed in columns D to J on the 'Staff_Details' tab)
d. the 'Activity Status' of the time entered shoud read 'Submitted' (see table AllData column M)

3. The output report shoud clearly identify each employee that is not compliant and show those days where the timesheet is missing or defective per item 2 above.  

4. The output report should have a row per employee stated in the department on the  'Staff_Details' tab and show the total time (hours) worked per day in accordance with the 'rules' above.

Notes
i. The time on the 'AllData' sheet is expressed in minutes so will need to be converted to hours in terms of time worked.
ii. Employees may make more than one entry on a particular day e.g. for each activity


Thanks in advance,
David
EE-Question.xls
0
Comment
Question by:davidascott
  • 9
  • 6
15 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39973442
Here is code to create the requested reports. The macro will create a PivotTable and analyze that to generate your missing timesheet & problems with hours entered reports.

As written, the code assumes that you are entering only one week of data at a time. Worksheet layout is critical. If your real workbook varies from the sample you posted, you should expect runtime errors.

Please test the code on the sample workbook attached to this Comment--it works in Excel 2013 on my computer. If it doesn't work at your end, please specify the version of Excel you are using.
Sub CreateReports()
Dim nm As Name
Application.ScreenUpdating = False
On Error Resume Next
Set nm = ActiveWorkbook.Names("StaffDetails")
If nm Is Nothing Then
    Set nm = ActiveWorkbook.Names.Add("StaffDetails", RefersTo:= _
        "=Staff_Details!$B$5:INDEX(Staff_Details!$K$5:$K$1000,COUNTA(Staff_Details!$B$5:$B$1000))")
End If
On Error GoTo 0

CreatePT
TestPivotTable
End Sub

Private Sub CreatePT()
Dim ws As Worksheet, wsPT As Worksheet
Dim rg As Range, rgPT As Range
Dim i As Long, j As Long, n As Long
Dim PT As PivotTable
Set ws = Worksheets("AllData")
On Error Resume Next
Set wsPT = Worksheets("Pivot Table")
If wsPT Is Nothing Then
    Set wsPT = Sheets.Add(After:=Worksheets(Worksheets.Count))
    wsPT.Name = "Pivot Table"
End If
Set PT = wsPT.PivotTables(1)
If Not PT Is Nothing Then
    Set rgPT = PT.TableRange1
    PT.ClearTable
    rgPT.Resize(1, rgPT.Columns.Count + 1).EntireColumn.Delete
End If
On Error GoTo 0
Set rg = ws.Range("A1").CurrentRegion
    
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & ws.Name & "'!" & rg.Address).CreatePivotTable _
    TableDestination:="'Pivot Table'!R3C1", TableName:="PivotTable1"
Set PT = wsPT.PivotTables("PivotTable1")
With PT
    With .PivotFields("Forename")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Surname")
        .Orientation = xlRowField
        .Position = 2
    End With
    With .PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
   .AddDataField .PivotFields("Duration (mins)"), "Sum of Duration (mins)", xlSum
    .ColumnGrand = False
    .RowGrand = False
    .PivotFields("Surname").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    .PivotFields("Forename").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    .RowAxisLayout xlTabularRow
    
    Set rgPT = PT.TableRange1
    Set rgPT = rgPT.Offset(1, 0).Resize(rgPT.Rows.Count - 1)
End With
rgPT.Cells(1, 10) = "Employee Index"
rgPT.Cells(2, 10).FormulaArray = _
    "=MATCH(1,(LOOKUP(""zzzzz"",A$5:A5)=INDEX(StaffDetails,,1))*(LOOKUP(""zzzzz"",B$5:B5)=INDEX(StaffDetails,,2)),0)"
Range(rgPT.Cells(2, 10), rgPT.Cells(rgPT.Rows.Count, 10)).FillDown
End Sub

Private Sub TestPivotTable()
Dim i As Long, j As Long, k As Long, kk As Long, n As Long
Dim dHours As Double, dDailyAllowed As Double, dWeekly As Double
Dim rgPT As Range, rgStaff As Range, rgReport1 As Range, rgReport2 As Range
Dim Forename As String, Surname As String
Dim v(1 To 4) As Variant, vNumber As Variant
Dim wsReport1 As Worksheet, wsReport2 As Worksheet
On Error Resume Next
Set wsReport1 = Worksheets("No timesheet")
If wsReport1 Is Nothing Then
    Set wsReport1 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    wsReport1.Name = "No timesheet"
    wsReport1.Range("A1:D1").Value = Array("Forename", "Surname", "Issue", "Date")
Else
    wsReport1.UsedRange.Offset(1, 0).ClearContents
End If
Set rgReport1 = wsReport1.Cells(2, 1)

Set wsReport2 = Worksheets("Timesheet problems")
If wsReport2 Is Nothing Then
    Set wsReport2 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    wsReport2.Name = "Timesheet problems"
    wsReport2.Range("A1:E1").Value = Array("Forename", "Surname", "Issue", "Date", "Particulars")
Else
    wsReport2.UsedRange.Offset(1, 0).ClearContents
End If
Set rgReport2 = wsReport2.Cells(2, 1)
On Error GoTo 0

Set rgStaff = ActiveWorkbook.Names("StaffDetails").RefersToRange
Set rgPT = Worksheets("Pivot Table").PivotTables("PivotTable1").TableRange1
Set rgPT = rgPT.Offset(1, 0).Resize(rgPT.Rows.Count - 1)
n = rgPT.Rows.Count
For i = 2 To n
    If rgPT.Cells(i, 1).Value <> "" Then Forename = rgPT.Cells(i, 1).Value
    If rgPT.Cells(i, 2).Value <> "" Then Surname = rgPT.Cells(i, 2).Value
    vNumber = rgPT.Cells(i, 10).Value
    If IsNumeric(vNumber) Then
        dDailyAllowed = rgStaff.Cells(vNumber, 3).Value / Application.CountIf(rgStaff.Cells(vNumber, 4).Resize(1, 7), "Yes")   'Hours
        dWeekly = Application.Sum(rgPT.Cells(i, 3).Resize(1, 7)) / 60   'Hours
        If dWeekly < rgStaff.Cells(vNumber, 3).Value Then
            kk = kk + 1
            rgReport2.Cells(kk, 1).Resize(1, 5).Value = Array(Forename, Surname, "Too few hours in week", "", _
                Format(dWeekly, "#.0") & " vs " & Format(rgStaff.Cells(vNumber, 3).Value, "#.0"))
        End If
        For j = 1 To 7
            dHours = rgPT.Cells(i, 2 + j).Value / 60
            Select Case LCase(rgStaff.Cells(vNumber, 3 + j))
            Case "yes"
                If dHours = 0 Then
                    k = k + 1
                    rgReport1.Cells(k, 1).Resize(1, 4).Value = Array(Forename, Surname, "No timesheet", rgPT.Cells(1, 2 + j).Value)
                ElseIf dHours < dDailyAllowed Then
                    kk = kk + 1
                    rgReport2.Cells(kk, 1).Resize(1, 5).Value = Array(Forename, Surname, "Too few hours in day", rgPT.Cells(1, 2 + j).Value, _
                            Format(dHours, "#.00") & " vs " & Format(dDailyAllowed, "#.00"))
                End If
            Case "no"
                If dHours <> 0 Then
                    kk = kk + 1
                    rgReport2.Cells(kk, 1).Resize(1, 5).Value = Array(Forename, Surname, "Hours reported on non-working day", rgPT.Cells(1, 2 + j).Value, _
                    Format(dHours, "#.00"))
                End If
            End Select
        Next
    End If
Next
End Sub

Open in new window

EE-QuestionQ28403279.xls
0
 

Author Comment

by:davidascott
ID: 39974798
Thanks very much for the response. Much appreciated. Feedback as follows:

1. No Time Sheet Report
Great - thank you.

2. Timesheet Problems Report
 Great - thank you

3. Pivot Table Tab - Report
(a) The numbers of staff on the 'Pivot Table' tab appear to exceed those stated on the 'Staff_Details' tab - please can the list be amended to show only those from the department?
(b) can the number of minutes to shown in hours please & summarise only only one row per person as listed in the 'Staff Details' tab - thx


Would like to do the following (sorry forgot to ask):

1. Select the name of the department on the 'Staff Details' tab as I will download differing list of staff for other departments (would it be possible to paste a list of departments in an admin area so that I could flex if there is organisational change?)
2. What would happen if the Surname increased by more than 3 characters? - I only used the first 3 to anonymise the data for data security reasons
3. What would happen if the Customer Column in AllData tab was changed?  - I only used numbers for confidentiality reasons
4. Will the reports cope with varying lines of data as this will vary constantly?
5. Will the reports cope with varying numbers of staff i.e. staff that leave the organisation will not be exported, so the list could grow and shrink

Best regards,
David
0
 
LVL 80

Expert Comment

by:byundt
ID: 39975174
David,
PivotTables are a great productivity tool, but the VBA code for them has changed with Excel versions. Which version of Excel are you using?

Responding to your additional requests:
A.  Restricting the PivotTable to members of the department only can be done, but won't really serve any purpose. I would need to add a column to the AllData worksheet with a formula saying whether person is a member of the department or not.
B.  Can the working time be summarized in hours rather than minutes? This can be done.
C.  Can you have only one row per person in the PivotTable? The PivotTable is already doing that. I didn't know which version of Excel you use, so I defaulted to the older way of building PivotTables, which doesn't repeat the Forename if it applies to different Surnames. In later versions, I can repeat the Forename on the same row as each Surname.

Item C raises the issue of whether you might prefer to see the list sorted by Surname, or possibly even displayed as Surname, Forename.

It should also be noted that most companies assign a unique employee number to each employee. Had a unique employee number been available in both AllData and Staff_Details worksheets, it would have simplified the PivotTable and subsequent analysis.

1.  Selecting the name of the department on the Staff Details tab can be done. Ideally, the user would be presented with a list of departments to choose from. A macro would then import the list of staff who belong to that department for the Staff_Details worksheet.
2.  If the Surname has more than 3 characters, nothing in the code would need to change--as long as both AllData and Staff_Details use the longer name. This need was anticipated.
3.  The Customer column of the AllData tab is ignored by the code. You may change it as you like.
4.  The code already handles varying number of rows of data in AllData worksheet. This need was anticipated.
5.  The code already handles varying numbers of staff in Staff_Details worksheet. This need was anticipated


The original question was substantially more complex than the usual ones posted in the Excel TA, as evidenced by the lack of response after you originally posted it. I enjoy challenges, and put almost four hours into the code posted in my first Comment.

The expansion of the project to include different departments also looks interesting, but I foresee a non-trivial amount of coding required to implement it. Since you appear to be developing a time sheet analysis "app," I suggest either putting the new requirement(s) into a new question on Experts Exchange, or else soliciting "work for hire."

Many of the more active Experts put email addresses in their member profile to facilitate making contact in "work for hire" situations. Note that the offer of such services and solicitation for them must be done outside the technical thread, as neither activity is permitted by the Terms of Service on Experts Exchange.

Regardless of how you want to proceed with these issues, please specify which version of Excel you are using.

Brad
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39977026
David,
I added the following features:
1. PivotTable restricted to employees listed in worksheet Staff_Details. I had to add an auxiliary formula column to worksheet AddData for this test.
2.  PivotTable shows accumulated time in hours rather than minutes
3.  Header labels on the reports now use Bold font
4.  Column widths on reports have been AutoFit
5.  Forename is repeated in PivotTable if two or more employees share that name. Depending on your version of Excel, this statement may cause a runtime error.

Brad

Sub CreateReports()
Dim nm As Name
Dim rg As Range
Dim ncols As Long
Application.ScreenUpdating = False
On Error Resume Next
Set nm = ActiveWorkbook.Names("StaffDetails")
If nm Is Nothing Then
    Set nm = ActiveWorkbook.Names.Add("StaffDetails", RefersTo:= _
        "=Staff_Details!$B$5:INDEX(Staff_Details!$K$5:$K$1000,COUNTA(Staff_Details!$B$5:$B$1000))")
End If
Set rg = Worksheets("AllData").Range("A2").CurrentRegion
ncols = rg.Columns.Count
If rg.Cells(1, ncols).Value <> "Dept Member?" Then
    ncols = ncols + 1
    rg.Cells(1, ncols).Value = "Dept Member?"
    rg.Cells(1, ncols).Font.Bold = True
End If
rg.Cells(2, ncols).FormulaArray = "=ISNUMBER(MATCH(1,(INDEX(StaffDetails,,1)=E2)*(INDEX(StaffDetails,,2)=F2),0))"
Range(rg.Cells(2, ncols), rg.Cells(rg.Rows.Count, ncols)).FillDown
On Error GoTo 0

CreatePT
TestPivotTable
End Sub

Private Sub CreatePT()
Dim ws As Worksheet, wsPT As Worksheet
Dim rg As Range, rgPT As Range
Dim i As Long, j As Long, n As Long
Dim PT As PivotTable
Set ws = Worksheets("AllData")
On Error Resume Next
Set wsPT = Worksheets("Pivot Table")
If wsPT Is Nothing Then
    Set wsPT = Sheets.Add(After:=Worksheets(Worksheets.Count))
    wsPT.Name = "Pivot Table"
End If
Set PT = wsPT.PivotTables(1)
If Not PT Is Nothing Then
    Set rgPT = PT.TableRange1
    PT.ClearTable
    rgPT.Resize(1, rgPT.Columns.Count + 1).EntireColumn.Delete
End If
On Error GoTo 0
Set rg = ws.Range("A1").CurrentRegion

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & ws.Name & "'!" & rg.Address).CreatePivotTable _
    TableDestination:="'Pivot Table'!R3C1", TableName:="PivotTable1"
Set PT = wsPT.PivotTables("PivotTable1")
With PT
    With .PivotFields("Forename")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Surname")
        .Orientation = xlRowField
        .Position = 2
    End With
    With .PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Dept Member?")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = "TRUE"
    End With
    .CalculatedFields.Add "Hours", "'Duration (mins)'/60"
    .AddDataField .PivotFields("Hours"), "Sum of Hours", xlSum
    .ColumnGrand = False
    .RowGrand = False
    .RepeatAllLabels xlRepeatLabels
    .PivotFields("Surname").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    .PivotFields("Forename").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    .RowAxisLayout xlTabularRow
    
    Set rgPT = PT.TableRange1
    Set rgPT = rgPT.Offset(1, 0).Resize(rgPT.Rows.Count - 1)
End With
rgPT.Cells(1, 10) = "Employee Index"
rgPT.Cells(2, 10).FormulaArray = _
    "=MATCH(1,(LOOKUP(""zzzzz"",A$5:A5)=INDEX(StaffDetails,,1))*(LOOKUP(""zzzzz"",B$5:B5)=INDEX(StaffDetails,,2)),0)"
Range(rgPT.Cells(2, 10), rgPT.Cells(rgPT.Rows.Count, 10)).FillDown
End Sub

Private Sub TestPivotTable()
Dim i As Long, j As Long, k As Long, kk As Long, n As Long
Dim dHours As Double, dDailyAllowed As Double, dWeekly As Double
Dim rgPT As Range, rgStaff As Range, rgReport1 As Range, rgReport2 As Range
Dim Forename As String, Surname As String
Dim v(1 To 4) As Variant, vNumber As Variant
Dim wsReport1 As Worksheet, wsReport2 As Worksheet
On Error Resume Next
Set wsReport1 = Worksheets("No timesheet")
If wsReport1 Is Nothing Then
    Set wsReport1 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    wsReport1.Name = "No timesheet"
    wsReport1.Range("A1:D1").Value = Array("Forename", "Surname", "Issue", "Date")
    wsReport1.Range("A1:D1").Font.Bold = True
Else
    wsReport1.UsedRange.Offset(1, 0).ClearContents
End If
Set rgReport1 = wsReport1.Cells(2, 1)

Set wsReport2 = Worksheets("Timesheet problems")
If wsReport2 Is Nothing Then
    Set wsReport2 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    wsReport2.Name = "Timesheet problems"
    wsReport2.Range("A1:E1").Value = Array("Forename", "Surname", "Issue", "Date", "Particulars")
    wsReport2.Range("A1:E1").Font.Bold = True
Else
    wsReport2.UsedRange.Offset(1, 0).ClearContents
End If
Set rgReport2 = wsReport2.Cells(2, 1)
On Error GoTo 0

Set rgStaff = ActiveWorkbook.Names("StaffDetails").RefersToRange
Set rgPT = Worksheets("Pivot Table").PivotTables("PivotTable1").TableRange1
Set rgPT = rgPT.Offset(1, 0).Resize(rgPT.Rows.Count - 1)
n = rgPT.Rows.Count
For i = 2 To n
    If rgPT.Cells(i, 1).Value <> "" Then Forename = rgPT.Cells(i, 1).Value
    If rgPT.Cells(i, 2).Value <> "" Then Surname = rgPT.Cells(i, 2).Value
    vNumber = rgPT.Cells(i, 10).Value
    If IsNumeric(vNumber) Then
        dDailyAllowed = rgStaff.Cells(vNumber, 3).Value / Application.CountIf(rgStaff.Cells(vNumber, 4).Resize(1, 7), "Yes")   'Hours
        dWeekly = Application.Sum(rgPT.Cells(i, 3).Resize(1, 7))    'Hours
        If dWeekly < rgStaff.Cells(vNumber, 3).Value Then
            kk = kk + 1
            rgReport2.Cells(kk, 1).Resize(1, 5).Value = Array(Forename, Surname, "Too few hours in week", "", _
                Format(dWeekly, "#.0") & " vs " & Format(rgStaff.Cells(vNumber, 3).Value, "#.0"))
        End If
        For j = 1 To 7
            dHours = rgPT.Cells(i, 2 + j).Value
            Select Case LCase(rgStaff.Cells(vNumber, 3 + j))
            Case "yes"
                If dHours = 0 Then
                    k = k + 1
                    rgReport1.Cells(k, 1).Resize(1, 4).Value = Array(Forename, Surname, "No timesheet", rgPT.Cells(1, 2 + j).Value)
                ElseIf dHours < dDailyAllowed Then
                    kk = kk + 1
                    rgReport2.Cells(kk, 1).Resize(1, 5).Value = Array(Forename, Surname, "Too few hours in day", rgPT.Cells(1, 2 + j).Value, _
                            Format(dHours, "#.00") & " vs " & Format(dDailyAllowed, "#.00"))
                End If
            Case "no"
                If dHours <> 0 Then
                    kk = kk + 1
                    rgReport2.Cells(kk, 1).Resize(1, 5).Value = Array(Forename, Surname, "Hours reported on non-working day", rgPT.Cells(1, 2 + j).Value, _
                    Format(dHours, "#.00"))
                End If
            End Select
        Next
    End If
Next
wsReport1.UsedRange.EntireColumn.AutoFit
wsReport2.UsedRange.EntireColumn.AutoFit
End Sub

Open in new window

EE-QuestionQ28403279.xls
0
 

Author Comment

by:davidascott
ID: 39979362
Amazing response - much appreciated
0
 

Author Comment

by:davidascott
ID: 39979363
sorry Brad - meant to select an A Grade - trying to undo. Best, David
0
 

Author Comment

by:davidascott
ID: 39979367
I have sent a message to the moderator asking for the grade to be A

best,
David
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:davidascott
ID: 39979377
Hi Brad,

I am using the 2010 version of Excel.

Best,
David
0
 

Author Comment

by:davidascott
ID: 39979384
Hi Brad,

I am not developing an app at all, nor will I commercially benefit from your great work.  I do however understand that it may appear like that due to the questions asked.

The work is to help me personally, which you have already done.

Thanks and regards,
David
0
 
LVL 80

Expert Comment

by:byundt
ID: 39979459
David,
I reopened the question per your request, acting in my capacity as Topic Advisor.


I did not mean to imply that you were developing a commercial application, and apologize for giving you that impression. I was instead imagining that you were developing a tool for department managers to help them review timesheets.

Quite a few people ask a related series of questions on Experts Exchange to help them build a tool for their own personal use, or for use by a small group in their organization. It's a great use of the site's capabilities.

One of the better examples of that was the "Great Calendar Project" by prettykittyq. She was building a tool to generate a work calendar for a very senior government official who had exacting requirements. Rather than using a never-ending supply of "Washingon intern" labor, she decided to automate it. She asked a long series of questions in different TAs on Experts Exchange, then posted a webpage describing how pleased she was with the result and the saga of how it came to be. That page is no longer on her site, but you can still view it in the Internet Wayback Machine archive of it at: https://web.archive.org/web/20070623034157/http://www.kittytours.org/calendarproject/ It's a good read.

Brad
0
 
LVL 80

Expert Comment

by:byundt
ID: 39979596
David,
Reverting to your question about how to adapt the code for multiple departments, I believe the easiest way to proceed is as follows:
1.  Add a column in the Staff_Details worksheet for the name of the department. If you put this column at the far right of the table, you won't need to modify any of the code.
2.  List all employees in all departments on the Staff_Details worksheet. If you want to see members of a single department, you can use an AutoFilter to filter the list for that department.
3.  Modify the line of code in the CreateReports sub that creates the named range StaffDetails. The modified line of code will be:
     Set nm = ActiveWorkbook.Names.Add("StaffDetails", RefersTo:= _
        "=Staff_Details!$B$5:INDEX(Staff_Details!$L$5:$L$1000,COUNTA(Staff_Details!$B$5:$B$1000))")
4.  Put a data validation dropdown in a cell that lets the user choose the name of the department to consider when running the code
5.  Modify the formula that is added to the AllData worksheet so it also tests whether the employee is a member of the chosen department. This will require modifying a single line of code in the CreateReports sub. The modified line of code would be:
rg.Cells(2, ncols).FormulaArray = "=ISNUMBER(MATCH(1,(INDEX(StaffDetails,,1)=E2)*(INDEX(StaffDetails,,2)=F2)*(INDEX(StaffDetails,,11)=N$1),0))"
This line of code assumes that the department name is in Staff_Details column L and that the chosen department is in AllData cell N1.

If you want to produce separate reports for each department, that could be done as well. You would probably want to have each department report go in a separate workbook. So you would need a macro that loops through all the departments and refreshes the PivotTable after each change. It wouldn't be hard to do, but definitely would involve a number of changes to the code.

Brad
0
 

Author Closing Comment

by:davidascott
ID: 39979700
Great solution and very elegant
0
 

Author Comment

by:davidascott
ID: 39979711
Thanks very much Brad.  I will give this the time it deserves this evening.

Thanks,
David
0
 
LVL 80

Expert Comment

by:byundt
ID: 39980080
Sample file with revised code so you can assign departments to employees on Staff_Details worksheet, and choose departments on AllData worksheet.

The named range Departments on worksheet Staff_Details will expand automatically with the number of departments. Just don't leave any blank cells between departments.
EE-QuestionQ28403279.xls
0
 

Author Comment

by:davidascott
ID: 39984541
Absolutely perfect - thank you Brad - best, David
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now