Project Workplan Dashboard

yosibest

New Member
Joined
Nov 9, 2013
Messages
6
Hi guys, I am very new to VBA and I am looking for some help with some code that would help me at work, basically I am looking to optimize a simple dashboard for my projects Workplan for the year.


The table on "2019 Plan" shows the number of projected days and to the right of it the actual days (which are picked up from each of the other tabs for each of the months).
The Chart tab has three bar charts
Chart 1 - Projected Vs Actual - This chart picks the data on columns N and AC to compare them
Chart 2 - Capacity - This chart picks up the sum on row 106
Chart 3 - Actual - Month - This chart picks up the sum of actual days used on each project from column V (the month will change on a monthly basis)


What I am trying to achieve, is to get Chart 1 and Chart 3 to ignore the rows where there are no projected days or no actual days used either, in other words and simplifying it, I would like the chart to ignore the zeros.


Is this something that could be achieved, if so would you be able to help me, it will really simplify my life, as I could just send the dashboard to my boss once a month and it will reduce the amount of time to work on a report.


I have included a link to the file.

https://www.dropbox.com/s/rgdff7ruvablu4q/Workplan 2019 - With Dashboard Draft_01.xlsx?dl=0



Thanks in advance.
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
608
I know its not VBA but maybe this helps.

I used an array on the data prep tab to extract Project Names greater than 0 only. Then pointed the data source for the Projected vs Actual at the data prep table.

The Actuals chart for june can be filtered by making a pivot table first that you can filter 0 and blanks and positive values.

My mock up is a different file name from yours so make sure not to copy over your original unless your happy with the end result.


https://www.dropbox.com/s/4ej3l8n0jx5lgqf/Workplan 2019 - With Dashboard Draft_sample_RG.xlsx?dl=0
 

Forum statistics

Threads
1,085,516
Messages
5,384,142
Members
401,883
Latest member
vidarv

Some videos you may like

This Week's Hot Topics

Top