+ Reply to Thread
Results 1 to 10 of 10

Workflow how to show this weeks tasks sum product/ pivot ?

  1. #1
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Workflow how to show this weeks tasks sum product/ pivot ?

    Hi All

    I have a master checklist of tasks to be completed in table form, its getting quite a lengthy document and I wondered if there a way to pull from the table just the tasks that were due to be done this week. Cells in bright red/ White Text represent this weeks tasks. I would need the result to be in the format on tab 2. I have manually inputted part of the result, this is just an example of some of the tasks needed to be done this week

    Anyone any ideas how to approach this, would like to keep the workflow table as its useful for other claculations

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    Hi foadavid,

    I have added a Pivot Table on Sheet2 below your "mock-up" - please see attached.
    Is this what you are after?

    Regards

    peterrc
    Attached Files Attached Files

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    This is just a pivot table. The magic comes from the three helper columns I added to the end of the table (Kudos for using a table) and the slicers that control the table. The helper columns compute year, month and week end date for the show date column.

    One thing about slicers is that they are self-cascading. That is, when I select a year, I get only those dates in that year. If I further narrow it down to a month, I get only dates for that month and I can further narrow those dates down by the week end date.

    The only trick I had to do is hide the pivot table header. It was not "holding" the vertical alignment, so I have a pseudo-header row.

    The magic is all in the slicers.

    P.S. Clear the slicer filter by clicking on the funnel icon on the top right of the slicer. You'll have to clear them in inverse order of setting them. For example, to reset the month, you'll have to clear the Week Ending Filter, etc.
    Attached Files Attached Files
    Last edited by dflak; 10-11-2017 at 02:15 PM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    Hi Peter

    Thanks for your reply, I really like the look of this, though when I have filtered each of the task columns by red colour
    which signifies this week it comes up with a different result? - Ive put these on a new tab, any ideas?
    Dave
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    Hi foadavid,

    The file you attached is not the one I worked on and attached to Post #2.
    I think your query should be addressed to dflak.

    Regards

    peterrc

  6. #6
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    Hi Peter
    Sorry about that I also had the same problem with the pivot table you sent me ie it didnt seem to pick up all the tasks. Ive attached the doc with a new tab which has the full list of tasks which i did manually

  7. #7
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    Hi Dflak
    Sorry I sent my reply to the wrong person, Thanks for your reply, I really like the look of this, though when I have filtered each of the task columns by red colour
    which signifies this week it comes up with a different result? - Ive put these on a new tab, any ideas?
    Dave
    Attached Files Attached Files

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    This will take several helper columns, but it's do-able. I don't have the time to get to it today or perhaps even tomorrow, but if you hang in there, I'll get to it

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    It took a lot of helper columns plus some VB code. I leave it to you to figure out how to copy the data into the table on the Workflow sheet.

    If you want, I'll explain what's going on. Otherwise just trust the magic. There are three slicers on the Tasks to Do sheet in columns P:U. Select the year and month and you will get a list of Week ending dates. You can select multiple values by holding down the CTRL key, so you can see everything due in October or the next two weeks or just this week. Click on the Get Schedule button.

    The time sheet contains a dynamic list of weekend dates for the past 3 years and next 3 years. It "rolls over" at year end. This list is what is used to drive the slicers. The Time, Item Pivots and Task Data sheets can be hidden.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Re: Workflow how to show this weeks tasks sum product/ pivot ?

    Sorry for the delay, huge thanks for this and the time spent doing it - its brilliant and much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. make a pivot from tasks per week
    By jaapaap in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 09-20-2017, 10:52 AM
  2. Replies: 0
    Last Post: 09-09-2014, 07:38 AM
  3. Show last 5 weeks in powerpivot
    By aftabn10 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-23-2014, 10:51 AM
  4. [SOLVED] Show Required Tasks
    By SeagullWardy in forum Excel General
    Replies: 2
    Last Post: 06-12-2012, 02:23 PM
  5. A way to show conflicting start/end date tasks
    By garricko in forum Excel General
    Replies: 2
    Last Post: 05-05-2010, 08:11 PM
  6. Replies: 1
    Last Post: 08-29-2006, 07:21 AM
  7. [SOLVED] Function to show percentage of tasks complete
    By Mirthrindr in forum Excel General
    Replies: 1
    Last Post: 07-31-2006, 09:18 AM

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