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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,683
Messages
5,626,275
Members
416,170
Latest member
Urraco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top