Log In
Register
Home
May 25, 2020
Offline
Hi Myndra,
Can you please help me I am stuck after watching your interactive dashboard for project management which I thought was wonderful, thank you for doing that for us.
I would like to change it to a monthly view however I am struggling to come up with similar formulas for the conditional formatting for a monthly view.
I have one that seems to work but when I allow room for growth its over spilling to areas where there is no data - how do I manage that please.
Thank you.
See AnswerMay 25, 2020
Offline
Hi Myndra,
I am trying to add a formula to meet the following criteria:
| Progress | Colour |
| 0% | Blue |
| 1% | Red |
| 40% | Amber |
| 70% | Green |
| 100% | Completed |
I couldn't attach the file as it was too big that's why I took screen shots so that you can see what I am trying to achieve. Thank you.
July 16, 2010
Offline
Hi Lisa,
Please see file attached. Note the order of the rules is important. Set them up starting with 0%. Check the Conditional Formatting Rules Manager in the example file if you have any problems.
Mynda
Answers Post
May 25, 2020
Offline
Hi Mynda,
Fantastic thank you so much for your time and help with this.
Can you design this dashboard to show you the start dates instead of the end dates such that you can select a view to show you projects starting instead of ending ones?
Also on the pivot table is there a way to sort with start dates?
Thanks,
Lisa
July 16, 2010
Offline
Hi Lisa,
Glad I could help.
Yes, you can base the dashboard off the start dates instead of the end dates, you'll just need to edit the formulas accordingly. And yes, you can sort the PivotTable on the start dates. More on sorting in PivotTables here.
Mynda
May 25, 2020
Offline
Hi Mynda,
Thank you, I have managed to sort my pivot table according. Sorry to keep coming back to you asking. How do I go about changing formulas to reflect the start of the project instead of the end?
Below is the
=AND($F6=0,AND($F6=0,AND($C6<>"""",MEDIAN($C6,$D6,G$5)=G$5)),TODAY()<$C6)
Above is the formula I was using for conditional formation.
I am completely lost on how to go about it. Can you please shed some light, please let me know if you need more information. My file is too big to upload so I have attached a picture instead.
Thanks,
Lisa
July 16, 2010
Offline
Hi Lisa,
What do you mean by 'reflect the start of the project' exactly? The conditional formatting in my file reflects the start to the finish by shading the cells for the weeks the project relates to. If you don't want that, then please provide an example Excel file showing exactly what you'd expect to see.
Note: you don't need to nest AND functions. You could simplify your formula to this:
=AND($F6=0,$C6<>"",MEDIAN($C6,$D6,G$5)=G$5),TODAY()<$C6) Mynda
May 25, 2020
Offline
Hi Mynda,
Thank you for providing a simpler formula. What I mean is, I would like to show all projects due to start by selecting using month slicer.
At the moment I have a slicer which shows me project due to end - the slicer is based on the end dates. I am happy with that.
I would like to replicate something similar to show projects starting dates - so have a slicer that enables me to select by month and it returns projects starting that month.
Please see desired screenshot.
July 16, 2010
Offline
Hi Lisa,
You can just add another Slicer for the start month field. That will filter the list for projects starting in the selected month/s.
Mynda
May 25, 2020
Offline
Fantastic the above has worked 🙂
Sorry to keep coming back. I hope this is the last time I come asking for help. I am nearly there now - I have used the formula you suggested above but the conditional formula seems to work for some but not all. Please see attached screenshot.
=AND($F6=0,$C6"",MEDIAN($C6,$D6,G$5)=G$5),TODAY()<$C6) - this is the formula I have used to highlight the timeline with 0% progress in blue. The screenshot shows formulas inserted manually for demonstration purposes only.
Thanks
Lisa
July 16, 2010
Offline
Hi Lisa,
The last argument in AND is TODAY()< C6 which is the start date which is effectively 11/11/2020 < 1/11/2020 which is FALSE, hence the result. I'm not sure why you have this criteria in the formula. Presumably you can just remove TODAY()< C6.
Mynda
1 Guest(s)
