+ Reply to Thread
Results 1 to 17 of 17

Reports by location and date range

Hybrid View

rushdenx1 Reports by location and date... 09-27-2010, 12:07 PM
JBeaucaire Re: Reports by location and... 09-27-2010, 01:29 PM
rushdenx1 Re: Reports by location and... 09-27-2010, 03:28 PM
JBeaucaire Re: Reports by location and... 09-27-2010, 03:40 PM
rushdenx1 Re: Reports by location and... 09-27-2010, 04:08 PM
JBeaucaire Re: Reports by location and... 09-27-2010, 04:30 PM
rushdenx1 Re: Reports by location and... 09-27-2010, 05:19 PM
JBeaucaire Re: Reports by location and... 05-19-2011, 02:07 PM
rushdenx1 Re: Reports by location and... 05-20-2011, 04:57 AM
JBeaucaire Re: Reports by location and... 05-20-2011, 03:22 PM
rushdenx1 Re: Reports by location and... 05-20-2011, 05:50 PM
JBeaucaire Re: Reports by location and... 05-20-2011, 07:31 PM
rushdenx1 Re: Reports by location and... 05-21-2011, 05:35 AM
JBeaucaire Re: Reports by location and... 05-21-2011, 10:34 AM
rushdenx1 Re: Reports by location and... 05-21-2011, 03:47 PM
JBeaucaire Re: Reports by location and... 05-21-2011, 10:37 PM
rushdenx1 Re: Reports by location and... 05-22-2011, 04:40 PM
  1. #1
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Reports by location and date range

    The enclosed file is an extract from a much larger file which has data added on a daily basis and can be any length. It shows performance data for many locations and by date. I need to be able to produce quick reports on request for any location and date range. Under the 'Location Report' tab it shows how I would like the Report to look for an exampled location, together with planned, run and on time numbers and %'s. Can someone advise me as to the best way on producing these reports quickly and easily. Any help is appreciated. Kevin.
    Attached Files Attached Files
    Last edited by rushdenx1; 09-27-2010 at 05:20 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    I've added a worksheet_change macro to this Location Report sheet so that any changes made in the yellow cells will cause the report to reassess.

    I've added a drop down in C1 to make location choices easy, the list is over in column M.

    I took a shot at the formulas in the green cells, not sure what's going on in the middle one.
    Last edited by JBeaucaire; 09-27-2010 at 04:31 PM. Reason: sheet removed...see below for latest version
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Thanks very much. That's Great. A couple of questions. I now want the Macro to work in the large master file I use, rather than the extract I supplied in the Forum. What changes need to be made to make it work. The Workbook is called 'PerformanceDataDump' and all the Data is contained on a Worksheet called 'Data' (it is in exactly the same layout as the Data worksheet I posted). The Report will still appear under a Worksheet called 'Location Report'. The list for the Drop Down box will be in column 'Z' not 'M' of the Location Report. I have tried to work it out for myself, but have failed miserably. Thanks Again. Kevin.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    Why not just copy your master data into the DATA sheet of the workbook supplied? The the macro works as is.

    You'll have to select C1 and go into the Data > Validation settings and carefully edit all the "M"s into "Z"s to get the dynamic expansion range formula to work in column Z instead.

  5. #5
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Thanks. I thought of that, but the master file contains a lot of other worksheets with Macros, so I thought doing it the other way round would be simpler. Kevin.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    Ok, I moved the data validation to column Z so you can see the new DV formula in C1.

    I've changed the macro to this:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    Dim wsData As Worksheet
    Dim wsWasOpen As Boolean:   wsWasOpen = True
    Dim wbRpt As String:        wbRpt = ThisWorkbook.Name
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("C1,C2,E2")) Is Nothing Then
        'open data wb if needed
        On Error Resume Next
            Set wsData = Workbooks("PerformanceDataDump.xls").Sheets("Data")
        On Error GoTo 0
        If wsData Is Nothing Then
            Workbooks.Open ("C:\2010\PerformanceDataDump.xls")
            Set wsData = Sheets("Data")
            ThisWorkbook.Activate
            wsWasOpen = False
        End If
        'import values
        Range("A7:G" & Rows.Count).Clear
        With wsData
            LR = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("AA1") = "Key"
            .Range("AA2:AA" & LR).FormulaR1C1 = _
                "=AND(RC10='[" & wbRpt & "]Location Report'!R1C3,RC5>='[" & wbRpt & "]Location Report'!R2C3,RC5<='[" & wbRpt & "]Location Report'!R2C5,RC18>0)"
            .Range("AA1").AutoFilter
            .Range("AA1").AutoFilter Field:=1, Criteria1:="TRUE"
            If .Range("A" & .Rows.Count).End(xlUp).Row > 1 Then _
                .Range("B2:B" & LR & ",E2:E" & LR & ",I2:I" & LR & ",K2:K" & LR & ",R2:R" & LR & ",T2:T" & LR & ",V2:V" & LR).Copy _
                    Range("A7")
            .AutoFilterMode = False
            .Range("AA:AA").Clear
        End With
    End If
    
    'close the data sheet if it wasn't already open
        If Not wsWasOpen Then Workbooks(wsData.Parent.Name).Close False
    'cleanup
        Set wsData = Nothing
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub

    This will open the data dump wb if needed. You need only edit the macro to provide the path to where the file is stored.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Many Thanks. Everything works. I have added to your reputation. Kevin

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    Post your followup questions here with appropriate notes and/or sample workbook.

  9. #9
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    I would like another Macro which is a slight variation to the one which has been kindly produced for the enclosed worksheet under the tab 'Location Report' This report currently shows all trains for a given date range that are going TO the location shown in the dropdown box (C1). What I would like is a new report under the 'Location Report Dep' tab which will show all trains that are FROM the location in C1, but instead of ORIGIN being shown it needs to be the various DESTINATIONS. I have shown the columns required under the 'Location Report Dep' tab. From the 'Data' tab, the columns required are Headcode (B), Dep Date (E), Destination (J), Dep (F), Act Dep (N), Dep +/- (P), Delay Reason (V). I have tried to adjust the letters myself but in the example shown 'Drax PS COAL' instead of the DESTINATION column showing all the various locations that the train from Drax go to, it just shows entries for trains going to Drax only and nowhere else. I hope you can understand my request. Any help is appreciated. Kevin.
    Attached Files Attached Files

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    Making sure you realize the "list" for the C1 drop down is off to the right in column Z on those two report sheets...
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Many Thanks. I have now added more entries to the master Data sheet and noticed that when there are two entries for the same train, where the first one only has the Departure time and the second only the Arrival time, the Location Report shows blanks against the Act Dep and +/- columns. Can the Macro be tweaked so that it shows the times for these two columns. Also does the list automatically pick up all locations on the Data sheet or do I have to manually update the list if a new location is used. I have enclosed the file to highlight the issue with the missing data. Thanks again. Kevin.
    Attached Files Attached Files

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    You should create a manual list of all the locations alphabetized into the list column.

    This should do it, the new sheet only looks at actual Departure rows, it brings over those rows. The Origin sheet brings over the rows that have an actual arrival times noted.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Many Thanks again. The actual Departure and +/- columns still remain blank for the double entries. The coding in the new sheet seems no different from the previous one. Kevin.

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    The previous code for departures skipped displaying rows that had no value in the data column R, that has been removed from that sheet and it now skips rows that have no value in column P, "Actual Dep". Quite different.

  15. #15
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Thanks for your continued help. I enclose a copy of the file which highlights the issue. If you look at the Location Report Dep tab it shows a report for Felixstowe with the Actual Dep and +/- columns blank. This should contain data from columns 'N' and 'P' on the 'Data' sheet. Sorry for taking up more of your time. Kevin.
    Attached Files Attached Files

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reports by location and date range

    I guess I'm losing my mind, I could've sworn I fixed this in previous testing, somehow uploaded the unfixed version again.


    In the Departure sheet macro, fix this one line of code, I underlined the edit:
            .Range("AA2:AA" & LR).FormulaR1C1 = _
                "=AND(RC9='[" & wbRpt & "]Location Report Dep'!R1C3,RC5>='[" & wbRpt & "]Location Report Dep'!R2C3,RC5<='[" & wbRpt & "]Location Report Dep'!R2C5,RC14>0)"

  17. #17
    Registered User
    Join Date
    07-24-2007
    Location
    London, England
    MS-Off Ver
    Office 365 ProPlus
    Posts
    80

    Re: Reports by location and date range

    Many Thanks. Everything works fine.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1