Present Rows from a separate sheet based on a Date Filter.

[mat0tam]

Hi all.

First post, after many months on the sideline i've decided to get involved.

I am working on summary sheet for my workbook. The workbook contains sheets (Sheets 2&3) with costs assessments for specific jobs (one per job currently).

In the job sheets the column headings are as follows:

Operation
No. of men
No. of hours
Total man hours
Hourly Cost
Total cost
Resource Discipline
Start Date
End Date
Average Daily Cost


I want to be able to enter two dates into a summary sheets (Sheet1) and it to pull through all the jobs that fall within the variables from each worksheet. Mainly to display the whole row, as from their I will easily be able to get the costs.

I foresee a lot of issues with sub headings, sub tasks etc as the job sheets are not a simple list.

Im not sure how to post up a spreadsheet (may be I cant as its my first post) but I feel seeing it would be the easiest way to explain it.

Im fairly competent in Excel but have limit VBa knowledge. I have a feeling Advanced filtering or a query macro of sorts is the way to go.

Thanks - Matthew.

 

[Jerry Sullivan]

Hi Matthew and Welcome to the Board,

This would depend largely on the specifics of how your data is organized.

As you noted the complexity of your data (sub headings and sub tasks) might make this a difficult problem.
An important factor is whether the data is consistent as even complex patterns can be managed through automation provided the patterns are predictable.

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM). The sharing site option would be a little better in your case so more people could potentially help.

 

[mat0tam]

Please see below the attached file im working on. Please not the is a SIMPLIFIED version but the essence is the same.
https://app.box.com/s/63lukprdm0ynf0n5hfst

 

[mat0tam]

Have used advanced filtering to drag data across but requires the whole data set to be reformatted into a no frills database without subheads, titles etc. This is now my plan B.

 

[Jerry Sullivan]

Matthew, I've been away on holiday - sorry for my delay in getting back to you.

A few questions after reviewing you example workbook.

1. How do you want to handle partially over-lapping dates? For example the search criteria is 8/8/2012 to 8/15/2012.
Should operation 2.01 on WP 26 which is listed as 8/10/2012 to 8/20/2012 get reported at 100% or a pro-rated value?

2. When listing a task item like WP 26 2.01 on the Task Snapshot sheet, what value should be listed in the Operation cell?
Is it just "Spray wall with PVA"
or
"2.01 Spray wall with PVA"
or something else?

Without a reference to Work Pack 26, there wouldn't be a distinction between 2.01 of one project and another.

3. The way that data is listed for Task 3 on the SubHeading Row instead of on the Task could make the problem more complex- depending on your response to question 2 above. Does it need to be that way.

4. Do you expect to have more than two sheets of projects in your actual application?

Overall, you can probably get the results you want from the workbook as it's currently laid out; however the if you have the ability to make some adjustments to the WP sheets, that could simplify the project.

 

[mat0tam]

Morning Jerry,

Appreciate your continued support.

1) I think its best that as the function of this filter is to provide an overview then reporting the task as 100% is sufficient.
2) I have added a column that identifies the sheet from where the data came from avoid confusion when multiple project are pulled in. (This will also be used to sort the data on the results page).
3) For task 3 I have combated this by copying the task information across all the tasks. They are now differentiated by the resource.
4) The final project is likely to include about 10 sheets.

Currently I have created a DATA sheet where all the tasks and costs are copied in without gaps, titles etc. The Adv. filter then pulls from this location. Its not the smartest way to work it but is functional; for now.

Matt

 

[Jerry Sullivan]

Matt, That helps clarify things- thanks.

The DATA sheet would greatly simplify things, but I presume you are manually creating that now which could be labor-intensive to maintain.

If you could post an updated example that shows the changes you've described (added column for sheet name, handling of task 3) and provide a link, then I'll suggest some VBA code. Please include a couple of populated rows in your Snapshot sheet that shows how the Operations field(s) are to be listed.

 

[mat0tam]

Hi Jerry, Apologies for the slow reply. Lets blame the timezones!

New Link: https://app.box.com/s/63lukprdm0ynf0n5hfst
(Please remember this is just Mock Data, as I cant post up the real data.

The data sheet in this resembles the actual, although in the actual, the data is a direct link and therefore automatically updates. It was created manually though and will not deal will with excessive changes to the source sheets.

As you may tell in the snapshot sheet an issue occurs where the top level task (WP26 - 2 in this occasion) does not appear as the criteria does not fully capture it. The desired requirement is; What work is taking place between these two dates. As you previously mentioned it would be great if I could get totals to be "proportional"? although this may be excessive at the current time.

Will check this post later and try to add any extra details I can.

Cheers - Matt.

 

[Jerry Sullivan]

Matt, I've uploaded a workbook at this link for you to try.

https://app.box.com/s/wj1h4302rji80326c3v6

The workbook uses an SQL query to get the records meeting your date criteria from each Project sheet.

I made some modifications to your file that make the query work better:

1. Changed criteria cells to be just dates with without "<=" operators (I understand you were using those for Advanced Filter).
2. Made header row consistent (row 6- was row 7 on one sheet)
3. Eliminated "." from field names
4. Added a header for the Task Number field.
5. Eliminated the Data sheet- since the Query handles the cleanup
6. Eliminated the Sheet Code from the data sheets- this field is added by the query.

Consistency is essential to making this approach work. That can be done through the combination of controlling the input data (train users, protect cells, data validation); and adding more exception handling to the VBA code. At this point, there's minimal control on either end so it would be prone to errors without further development.

Here's the code for the benefit of anyone not downloading the example file....

Sub QueryData()
'---Uses query to copy records meeting criteria to SnapShot sheet

    Dim oConnection As Object
    Dim oRecordset As Object
    Dim sSQL As String, sDataAddress As String
    Dim lPart As Long
    Dim vUnionParts As Variant
    Dim dtStart As Date, dtEnd As Date
    Dim ws As Worksheet

    
    With Sheets("Task Snapshot")
    '--get criteria
        dtStart = .Range("F10")
        dtEnd = .Range("G10")
    End With

    
    ReDim vUnionParts(1 To Worksheets.Count)

    
    '---build query text using parameters for each sheet
    For Each ws In ActiveWorkbook.Worksheets

        
        If ws.Name <> "Task Snapshot" Then
            '--could add further validation steps here
            With ws
                sDataAddress = .Name & "$B6:M" & _
                    .Cells(.Rows.Count, "B").End(xlUp).Row
            End With

            
            lPart = lPart + 1

            
            vUnionParts(lPart) = Join$(Array( _
                "SELECT '" & ws.Name & "' AS [WPCode], [Task Number],", _
                    "[Operation],[No of men],[No of hours],", _
                    "[Total man hours],[Hourly Cost], [Total Cost],", _
                    "[Resource Discipline] , [Start Date], [End Date]", _
                "FROM [" & sDataAddress & "]", _
                "WHERE [Start Date]<=#" & dtEnd & "#", _
                    "AND [End Date]>=#" & dtStart & "#" _
                 ), vbCr)
        End If
    Next ws

    
    ReDim Preserve vUnionParts(1 To lPart)
    '--union parts from each sheet into one query
    Select Case lPart
        Case Is > 1
            sSQL = Join$(vUnionParts, vbCr & "UNION ALL" & vbCr)
        Case 1
            sSQL = vUnionParts(1)
        Case Else
            Exit Sub
    End Select

    
    Set oConnection = CreateObject("ADODB.Connection")


    With oConnection
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Extended Properties").Value = "Excel 8.0"
      .Open ActiveWorkbook.FullName
    End With

         
    Set oRecordset = CreateObject("ADODB.Recordset")

    
    oRecordset.Open Source:=sSQL, _
        ActiveConnection:=oConnection, _
        CursorType:=3, _
        LockType:=1, _
        Options:=1


    With Sheets("Task Snapshot")
        '--clear any existing data then paste recordset
        .Range("14:" & Rows.Count).ClearContents
        .Range("C14").CopyFromRecordset oRecordset
    End With


    oRecordset.Close
    oConnection.Close
    Set oRecordset = Nothing
    Set oConnection = Nothing
End Sub

 

[mat0tam]

Thanks Jerry, this is perfect. admittedly I don't understand the later part of the code;

'--union parts from each sheet into one query down to where the data is cleared and pasted.

I'm sure I can make some mild edits to my spreadsheet to ensure this macro works for the long term.

I will keep you posted on how it goes. Im looking at tweaking the code to allow for selection of tasks performed by specific resource's too (Will try to figure this out myself initially).

(Will do a Google search now) Are you aware of any way to auto run some conditional formatting? So for instance highlight any tasks that are the "top 5" costs for their representative sheet?

Really appreciate the help - Makes me wish I had the time to delve more into the uses of VBa. (any recommended sites/tutorials)