Hi there. I built a PM Dashboard (Thank you Mynda!). Management have requested that the dashboard only displays a 6 weeks spread. Two weeks in the past from today's date and 4 weeks in the future of today's date. Tab - Storage M5 & M6, hold the start and end date of the 6 weeks spread from today's date. Tab Dashboard - L9 using Storage M5 to start off the calendar display.
In addition to only showing the 6 week spread, they would like the task list to only display those tasks that are active and fall within that date range.
- if a task started 8 weeks ago, but is still "in progress", it should show
- display those tasks that are due to be completed during that period.
- display those tasks that are due to commence during that period.
So is there a way to "filter" the pivot table within the Dashboard tab, based on the values held in Storage M5 & M6.
The intent is the dashboard, anytime it is viewed, displays the "current" six weeks (2 in arrears and 4 in advance of Today() ).
I've attached a sample file of what I've done. I hope this makes sense.
Thank you.
Sue
The attachment didn't attach. Don't forget to hit 'Start Upload' and wait for the grey check mark to appear before hitting 'Submit'.
What version of Excel do you have? if you have 365 do you need backward compatibility with earlier versions of Excel, asking to know if you can use some of the new formulas. Another thought I had was using power query, would that be an option? Just a couple of ideas to get your creative juices flowing whilst we wait for the attachment. 🙂
Using Excel 2019. Hopefully the file is attached this time.
Cheers
Sue
So I think I figured it out, currently doing some test to make sure, but so far, so good.
=IF(AND($D4=Storage!$M$6),"yes", IF(AND($E4>=Storage!$M$5,$E4=Storage!$M$5,$D4<=Storage!$M$6),"yes","no")))
(Storage M5 holds the Monday of two weeks in arrears from today's date and Storage M6 holds the Friday of 4 weeks ahead of today's date.
On the Data Storage tab, I added in a new column within the table, using this formula. The column then holds a yes, to those tasks that meet the parameter.
On the Dashboard, within the pivot table, I used the new column as a filter only (doesn't look pretty, having the filter displaying), but at least the task list within the pivot only includes the tasks that fit within the session.
If anyone has a neater way of doing this, please let me know.
Note. I had the logics statements in reverse order and it didn't work, so swapped logic statement 3 with statement 1 and it then worked.
Cheers
Sue
