No announcement yet.

Extract Information based on TODAY

  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract Information based on TODAY

    Good morning (from US Central),

    I would like to have data move from a worksheet to another sheet. For the attached example. i would like the information on the "current" sheet for any project that is taking place on the today. I would like it to go to the "Compiled Job Data-After" sheet. The date reference is in cell B1 and the field I would like to auto-populate are shown in cells A2:I2. I believe that this data would come from the dark blue cells in the worksheet "Current" columns B:C as long as the data in columns D through until project completion shows green fill or green to red fill.

    From the attached:

    Worksheet "Current" - Rows 34:39 - Job #1 - for 10/19/2018 would pull populate the data shown on the worksheet "Compiled Job Data-After" row 3.

    Then this would continue for all jobs that are taking place on that day. When the project was over it would fall off the report on "Compiled Job Data-After".

    Thanks and sorry if I left anything out.
    Attached Files

  • #2
    Hello Central US,

    try this solution. Press update btn on Data-After sheet. Macro clears current rows and depending on the date in B1 inserts required info. Didn't care about error handling much, macro fits to the given example, you find some constants in the module that might be changed ( but some are hard coded).


    Schedule Gantt Chart_1.xlsm


    • #3
      That works perfectly. I tested it a couple of times and no errors.

      Thank you.


      • #4

        So, fast forward a month, the solution is bugging out a bit. I have saved the attached file with today's date in cell D24. The date in the original file was 10/1/18. I would like to update cell D24 to the first monday of each month. I don't think that has a huge weight on the solution but I just want that to be known. When I update cell D24 to the first monday, it updates column G data on the Compiled Job Data-After tab to "Start Day" which is the first column to the left of actual project data (column C). I can't figure out the code to fix it. I would like the Compiled After tab to show only work that is taking place on the date in cell B1. I can have up to 12-15 jobs in a day that need to be populated from the Current tab to the Compiled After tab.

        Attached Files